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.

MySQL cannot use an index when you have a `%` wildcard at the start of the string. If your query was `LIKE 'cake%'`, the index would be used. — Michael Berkowski, Jun 19 '15 at 17:45
@MichaelBerkowski: If you know the answer please write it as one. Comments are for requesting clarification. — Lightness Races in Orbit, Jun 19 '15 at 17:46
@LightnessRacesinOrbit I'm searching for the duplicate I know exists. Hold tight... — Michael Berkowski, Jun 19 '15 at 17:46
I understood, but since I can't use indexes how I can improve my search query? — Fabio, Jun 19 '15 at 17:50
Thanks to everyone. Since MySQL 5.6 introduced Full Text Indexes for InnoDB, I will use it. Just a question @MichaelBerkowski: Have I to delete my question since it's duplicated? — Fabio, Jun 19 '15 at 18:13
@Fabio No, it's fine to leave it, because it will turn up in searches to point to more complete answers. (especially if I edit the title a little) — Michael Berkowski, Jun 19 '15 at 18:17

score 6 · Answer 1 · answered Jun 19 '15 at 17:45

6

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

answered Jun 19 '15 at 17:45

juergen d

201,996
37
293
362

score 4 · Accepted Answer · answered Jun 19 '15 at 17:45

4

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.

answered Jun 19 '15 at 17:45

tadman

208,517
23
234
262

How I can use a full-text index? – Fabio Jun 19 '15 at 17:52
Like anything, step one is [reading the documentation on full-text indexes](https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html). – tadman Jun 19 '15 at 17:53

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

2 Answers2