How to speed up SELECT .. LIKE queries in MySQL on multiple columns?

I have a MySQL table for which I do very frequent SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%' queries. Would any kind of index help speed things up?

There are a few million records in the table. If there is anything that would speed up the search, would it seriously impact disk usage by the database files and the speed of INSERT and DELETE statements? (no UPDATE is ever performed)

Update: Quickly after posting, I have seen a lot of information and discussion about the way LIKE is used in the query; I would like to point out that the solution must use LIKE '%text%' (that is, the text I am looking for is prepended and appended with a % wildcard). The database also has to be local, for many reasons, including security.

Solution 1:

An index wouldn't speed up the query, because for textual columns indexes work by indexing N characters starting from left. When you do LIKE '%text%' it can't use the index because there can be a variable number of characters before text.

What you should be doing is not use a query like that at all. Instead you should use something like FTS (Full Text Search) that MySQL supports for MyISAM tables. It's also pretty easy to make such indexing system yourself for non-MyISAM tables, you just need a separate index table where you store words and their relevant IDs in the actual table.

Update

Full text search available for InnoDB tables with MySQL 5.6+.

Solution 2:

An index won't help text matching with a leading wildcard, an index can be used for:

LIKE 'text%'

But I'm guessing that won't cut it. For this type of query you really should be looking at a full text search provider if you want to scale the amount of records you can search across. My preferred provider is Sphinx, very full featured/fast etc. Lucene might also be worth a look. A fulltext index on a MyISAM table will also work, but ultimately pursuing MyISAM for any database that has a significant amount of writes isn't a good idea.

Solution 3:

An index can not be used to speed up queries where the search criteria starts with a wildcard:

LIKE '%text%'

An index can (and might be, depending on selectivity) used for search terms of the form:

LIKE 'text%'

Solution 4:

Add a Full Text Index and Use MATCH() AGAINST().

Normal indexes will not help you with like queries, especially those that utilize wildcards on both sides of the search term.

What you can do is add a full text index on the columns that you're interested in searching and then use a MATCH() AGAINST() query to search those full text indexes.

1. Add a full text index on the columns that you need:

   ALTER TABLE table ADD FULLTEXT INDEX index_table_on_x_y_z (x, y, z);
   

2. Then query those columns:

   SELECT * FROM table WHERE MATCH(x,y,z) AGAINST("text")
   

From our trials, we found these queries to take around 1ms in a table with over 1 million records. Not bad, especially compared to the equivalent wildcard LIKE %text% query which takes 16,400ms.

Benchmarks

MATCH(x,y,z) AGAINST("text") takes 1ms

LIKE %text% takes 16400ms

16400x faster!