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.