Performance Gains using MySQL Full-Text Searches
May 09, 2007A recent project required me to do searching on some text fields stored in a MySQL database. In the past I’ve used statements like this:
SELECT name FROM people WHERE name LIKE '%bob jones%';
Now this works fine for smaller sets of data, but when you suddenly have data sets with millions of records it no longer works (at least if you remotely care about performance). A normal index won’t do much good either, since MySQL won’t use an index if your search string has a leading wildcard.
The solution? MySQL’s full-text search.
First thing to do is to add the index to the table:
ALTER TABLE people ADD FULLTEXT INDEX name_index(name);
Now you can search against the table like this:
SELECT name FROM people WHERE MATCH(name) AGAINST ('"bob jones"' IN BOOLEAN MODE);
Now I’m using boolean searches to get an exact match on the name “bob jones”, but you can drop the boolean mode and you’ll get search results based off relevancy. MySQL actually has some pretty good documentation on everything you can do with full-text searches.
In my research I was seeing incredible performance improvements, I had queries which were previously taking 250-275 seconds reduced to 0.5 seconds.
Also, as always, be aware that your inserts will take longer since the table will have to regenerate indexes with every insert. The MySQL docs has a good section on how to increase the speed of your inserts to minimize the hit you take from using indexes.