Alex Rivera | Logout

Most efficient way to search in SQL?

Asked 2012-03-06T18:36:20.847
15

I have a database with 75,000+ rows with 500+ entries added per day.

Each row has a title and description.

I created an RSS feed which gives you the latest entries for a specific search term (ex. http://site.com/rss.rss?q=Pizza would output an RSS for the search term "Pizza").

I was wondering what would be the best way to write the SQL query for this. Right now I have:

SELECT * 
FROM 'table' 
WHERE (('title' LIKE %searcherm%) OR ('description' LIKE %searcherm%))
LIMIT 20;

But the problem is it takes between 2 to 10 seconds to execute the query.

Is there a better way to write the query, do I have to cache the results (and how would I do that?) or would changing something in the database structure speed up the query (indexes?)

mysql sql
Edit
Report

1 Answer

5

If you're using a query with LIKE '%term%' the indexes can't be used. They can be used only if you use a query like 'term%'. Think about an address book with tabs, you can find really fast contacts starting with letter L, but to find contacts with a on somewhere in the word, you've to scan the whole addressbook.

The better alternative could be to use full text indexes:

CREATE FULLTEXT INDEX title_desc
ON table (title, description)

And then in the query:

SELECT title, description FROM table
WHERE MATCH (title, description) AGAINST ('+Pizza')
answered 2012-03-06T18:44:18.733

Your Answer