Sometimes MySQL gets it wrong. It doesn’t use the right index.
It happens that MySQL generates a query plan which is really bad (EXPLAIN says it’s going to explore some 10,000,000 rows), when another plan (soon to show how was generated) says: “Sure, I can do that with 100 rows using a key”.
A true story
A customer had issues with his database. Queries were taking 15 minutes to complete, and the db in general was not responsive. Looking at the slow query log, I found the criminal query. Allow me to bring you up to speed:
A table is defined like this:
CREATE TABLE t ( id INT UNSIGNED AUTO_INCREMENT, type INT UNSIGNED, level TINYINT unsigned, ... PRIMARY KEY(id), KEY `type` (type) ) ENGINE=InnoDB;
The offending query was this:
SELECT id FROM data WHERE type=12345 AND level > 3 ORDER BY id
The facts were:
- `t` has about 10,000,000 rows.
- The index on `type` is selective: about 100 rows per value on average.
- The query took a long time to complete.
- EXPLAIN has shown that MySQL uses the PRIMARY KEY, hence searches 10,000,000 rows, filtered “using where”.
- The other EXPLAIN has shown that by using the `type` key, only 110 rows are expected, to be filtered “using where”, then sorted “using filesort”
So MySQL acknowledged it was generating the wrong plan. The other plan was better by its own standards.
Solving the problem
Let’s walk through 7 ways to solve the problem, starting with the more aggressive solutions, refining to achieve desired behavior through subtle changes. Continue reading » “7 ways to convince MySQL to use the right index”