Most efficient way to search in SQL?

Question

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?)

Answer 1

A relatively simple solution for this would be incorporating a FULLTEXT index on these two fields and subsequently searching by using this index.

ALTER TABLE table ADD FULLTEXT(title, description);

Then would you need to perform a search, you'd do the following:

SELECT id FROM table
WHERE MATCH (title, description) AGAINST ('keyterm');

Fulltext indexed search is the automatic solution included in most SQL databases. It's much speedier comparing to doing LIKES. This is also optimized for your specific case because you are only interested in natural language search terms.

As well, fulltext index has some limiting algorithm for detecting relevancy. You can read more about it here

EDIT

In the alter statement, I missed the fulltext index name, it should be:

ALTER TABLE table ADD FULLTEXT ft_index_name(title, description);

Answer 2

Try:

SELECT * FROM table
WHERE MATCH (title,description) AGAINST (searchterm);

Make sure you add a full text index on title, description together.

Dont try to reinvent the wheel. MATCH and AGAINST are provided by mysql to do exactly that and to make your life easy. However, note full text search works on MyISAM tables. You can workaround for InnoDb too. You can simply add FT index by altering table like:

ALTER TABLE table ADD FULLTEXT(title,description);

Answer 3

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')

Answer 4

I would go with JohnB's or gtr32x's answer (Full Text Indexing). To complement their answer, there's a manual way to create a simple full text index that's simple and it's super fast...

Split title and description into keywords, and place them in a Keywords table, which has a foreign key to the original RSS article. Make sure the keyword column in Keywords is indexed. The you can do something like:

SELECT DISTINCT ra.* 
FROM RssArticle ra
INNER JOIN Keywords k ON k.ArticleID = ra.ArticleID
   WHERE k IN ( 'SearchTerm1', 'SearchTerm2', 'SearchTerm3')
LIMIT 20;

And it's fast!

Answer 5

Try either of the following four queries:

select * from myTable where concat_ws(' ',title,description) like '%pizza%';
select * from myTable where concat_ws(' ',title,description) regexp '.*pizza+.*';
select title,description from myTable where concat_ws(' ',title,description) like '%pizza%';
select title,description from myTable where concat_ws(' ',title,description) regexp '.*pizza+.*';

the point is to use concat before searching

Answer 6

A few pointers: Drop the * in your select statement and pull only the searched criteria, and make sure to add indexes to the columns that are getting searched.

SELECT `title`,`description` 
FROM `table` 
WHERE `title` LIKE '%$searchterm%' OR `description` LIKE '%$searchterm%' LIMIT 25;

Answer 7

1. Did you create an index for title and for description?
2. You should consider Sphinx for Full Text Search capabilities.

---

Thanks for the comment Tyler.

I restate my answer:

1) Create an index on title and description columns, but your query would be limited to the example below, and that's not ideal for finding all relevant rows:

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

2) As others have mentioned, use MySQL Full-Text Search, but you are limited to MyISAM table engine, as it isn't available for InnoDB. However, you can mix engines in MySQL, so you can make this table MyISAM, even if all your other tables are InnoDB.

3) Use an external Full-Text Search engine, such as Sphinx. This will give you more relevant search results (MySQL Full-Text Search leaves much to be desired), it will perform better, and it abstracts the burden of Full-Text Searching away from your database.