> Martin Polden

MySQL and fast wildcard search

I recently had some speed issues with MySQL and wildcard searching. I wanted to retrieve results from a table containing over 2.7 million rows using a simple SELECT FROM table WHERE col LIKE '%string%'; query. The problem was that this took forever and MySQL kept thrashing the hard drive because it couldn't optimize the query in any way.

First I started looking into the most natural solution: Full text search. Creating a FULLTEXT index on the table and changing the query slighty, using MATCH instead of LIKE speeded up the query a bit, but it still wasn't enough.

Being not too satisfied with the fulltext search I just ended up creating a temporary copy of the table using the MEMORY storage engine and only included the fields I needed to search. That way the query happens in-memory and the disk thrashing is avoided. Afterwards, I made my script insert new rows in the temporary table aswell as the on-disk one, to keep them in sync.