CODEWITHSUNDEEP
phpjavascriptmysqlsql
wong2
wong2

Reputation: 35720

how could I increase the speed of my search suggest?

I add a search suggest for my site just like Google Suggest, but the speed is a litte slow ... I did it in this way: When the user type in the search bar, I use Ajax to pass the keyword to a php file, which search the keyword in the database(MySql) with statement like this:

 SELECT DISTINCT keywords
 FROM  `searchrecord` 
 WHERE keywords LIKE '$keywords%'

the table 'searchrecord' has about 500,000 records.

But finally, the speed is almost unbearable, I have to wait for a long while to see the suggest words pop out . Do you have any suggestion about how to speed this up?

Upvotes: 0

Views: 168

Answers (2)

regilero
regilero

Reputation: 30496

A like query with '%' at the end should be fast if you have an index on the column. This is really the best way to use an index. But if you add a '%' at the begining of the search you'll effectively loose all your index speed (and you'll have to use full text search, which will prevent case problems as well)

You should as well check the security of your query, we all hope you $keywords variable as been sanitized before usage.

And if keywords is a list of keywords your query should be :

WHERE keyword IN ('key1','key2','key3')

Or:

WHERE keyword LIKE 'key1%' OR keyword LIKE 'key2%' OR keyword LIKE 'key3%'

Upvotes: 1

Haim Evgi
Haim Evgi

Reputation: 125466

what i recommended is o use mysql full text search

you can look on web for tutorials .

a good article :

http://devzone.zend.com/article/1304

Upvotes: 1

Related Questions