FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


PHP search function with MySQL fulltext





Peterssidan
Imagine a php search function using mysql fulltext. If the search get many hits it's preferable to show some of them on different pages. What is the best way to handle this?

Of course it's possible to make the search again if the user go to next page but isn't that inefficient? Does mysql cache recent searches so that the second search would run quite fast or do I have to implement some kind of cache myself? Or is that just a bad idea?
badai
what php + mysql do behind the scene is too fast. if you have 1 million rows in your table, you can just do a loop of query 10 times just for fun and return just the last result back to browser.

what make it slow is for your web server to transfer the plain text back to browser.

yes, mysql do have cache, but it's kind of useless in most situation. its use mostly is to handle the exact query (case sensitive) from millions of clients, so that mysql will not query again, and just return the same result from the previous query, provided the table has not changed from the last time.

in your case, do "paging" where every query will use limit, and you will get hit as much as your lower and upper limit permit. the way you explain your problem, it's like you don't use limit in your query, and it's your script that filter the result to show it in different pages. that's a no no. your script engine won't have enough allocated memory for it to store the result.
rvec
mysql queries are slow. Not as slow as the browser, but if you use a lot of queries in huge tables it might take up seconds.
badai
that is why u use limit.

other way to "speed up":

http://dev.mysql.com/doc/refman/5.0/en/query-speed.html

not really speed up, but help you query as less as possible.

rvec wrote:
mysql queries are slow. Not as slow as the browser, but if you use a lot of queries in huge tables it might take up seconds.
Peterssidan
But if I do a fulltext search, mysql will still have to search everything even if I use a limit. right? And another problem with limit. If I use limit how will I know how many extra pages there are?
badai
i think the best you can do now is google for php + mysql + paging. i'm sure there a lots need to be learn and understand before you can accomplish whatever you want to do now.

read here about "full text" search
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

you never "do" full text search, you just set your table to be search full text. see this:
http://dev.mysql.com/doc/refman/5.0/en/indexes.html

anyway, if you got a term that you don't really understand, you should "ask yourself" to define it first. "asking yourself" is not to get the answer yourself, just google for it, and ask here to confirm if you are not sure. failure to understand the term can effect a lot on your assignment marks if you do the wrong thing.
rockacola
Peterssidan wrote:
But if I do a fulltext search, mysql will still have to search everything even if I use a limit. right? And another problem with limit. If I use limit how will I know how many extra pages there are?


Are you trying to do pagination with your search result? pete.
If so, normally you would do 2 queries, 1 to find out the number of matching result, and run the same query again to get the rows you want.
badai
the first query should use count, so it won't use up resource in mysql.

the second query should use limit, otherwise it will return the whole rows.
Related topics
php admin and mysql admin console
Make search engine With PHP and mySQL, for your site
PHP Mail Function
What to do BEFORE making a new topic!
Site Nav Suggestions...
Search function
mysql table keyword search
PHP mail function ?
Search function server load relief.
PHP mail() Function
SMTP, sendmail e PHP mail function
Warning: mysql_pconnect() [function.mysql-pconnect]:
Database for php
[URGENT!!] MySQL not Displaying Queries Correctly
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.