Why is an index not used on a LIKE query with wildcards?

Question

Although the Title column was added as index by using the following query:

ALTER TABLE Recipe ADD INDEX Title_idx (Title)

MySQL doesn't use that index for this query:

SELECT * FROM Recipe
WHERE Title LIKE '%cake%';

I used the EXPLAIN keyword and the key field is NULL.

How to solve it? I have to improve that query.

Answer 1

You need a full-text index to match partially. Consider a normal index like a phone book: It's great for finding people by last name then first name, say "Smith, John", but useless for finding people with "ith" in their name, you'll have to go through entry by entry manually matching.

Any query that says LIKE '%x%' will automatically be a table scan. This will not scale for non-trivial sized tables.

Answer 2

Because an index start from the beginning of a string. Since you look for a substring at ANY position in the title, the index can't be used.

This could make use of an index

WHERE Title LIKE 'cake%';

but this not

WHERE Title LIKE '%cake%';