The most efficient way to search in SQL?

Question

I have a database with 75,000 + rows with the addition of more than 500 entries per day.

Each line has a title and description.

I created an RSS feed that gives you the latest entries for a specific search query (for example, http://site.com/rss.rss?q=Pizza will display RSS for the search query "Pizza").

I was wondering what would be the best way to write an 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 that it takes 2 to 10 seconds to complete the request.

Is there a better way to write a query, do I need to cache the results (and how to do this?) Or change something in the database structure to speed up the query (indexes?)

Answer 1

A relatively simple solution for this would be to include the FULLTEXT index in these two fields and subsequently search using that index.

 ALTER TABLE table ADD FULLTEXT(title, description); 

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

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

Full-text indexed search is an automated solution included in most SQL databases. This is much faster compared to running LIKES. It is also optimized for your specific case, because you are only interested in natural language search terms.

In addition, the full-text index has some restrictive algorithm for determining relevance. You can read about it here.

EDIT

In the alter instruction, I skipped the full name of the index, 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 the full text index in the title, description together.

Do not try to reinvent the wheel. MATCH and AGAINST provided by mysql to do just that and make your life easy. However, note that full-text search works in MyISAM tables. You can get around the workaround for InnoDb. You can simply add the FT index by modifying the table as follows:

 ALTER TABLE table ADD FULLTEXT(title,description); 

Answer 3

If you use a query with LIKE '%term%' , indexes cannot be used. They can be used only if you use a query like 'term%' . Think of a tabbed address book, you can find really fast contacts starting with the letter L , but to find contacts with on somewhere in the word, you must scan the entire address book.

A better alternative would be to use full-text indexes:

 CREATE FULLTEXT INDEX title_desc ON table (title, description) 

And then in the request:

 SELECT title, description FROM table WHERE MATCH (title, description) AGAINST ('+Pizza') 

Answer 4

I would go with a JohnB or gtr32x (Full Text Indexing) answer. In addition to their answer, there is a manual way to create a simple full text index that is simple and very fast ...

Divide the title and description into keywords and place them in the Keywords table, which has a foreign key to the original RSS article. Make sure the keyword columns in Keywords indexed. 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 one 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+.*'; 

point must use concat before searching

Answer 6

A few pointers: drop * in your select clause and pull only on the criteria found and be sure to add indexes to the column search.

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

Answer 7

• Have you created an index for title and for description ?
• You should consider Sphinx for full-text search.

---

Thanks for the comment Tyler.

I repeat my answer:

1) Create an index in the title and description columns, but your query will be limited to the example below and is not ideal for finding all the relevant rows:

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

2) As already mentioned, use MySQL Full Text Search , but you are limited to the MyISAM table engine because it is not available for InnoDB. However, you can mix engines in MySQL, so you can make this MyISAM table even if all of your other tables are InnoDB.

3) Use an external full-text search engine such as Sphinx . This will give you more relevant search results (full-text search in MySQL leaves much to be desired), it will work better, and it abstracts the burden of full-text search from your database.