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

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

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%';