MySQL Explain vs Slow Log
Using MySQL (5.1.66) explain says it will scan just 72 rows while the "slow log" reports the whole table was scanned (Rows_examined: 5476845) How is this possible? I can't figure out what's wrong with the query
*name* is a string unique index and *date* is just a regular int index
This is the EXPLAIN EXPLAIN SELECT * FROM table WHERE name LIKE 'The%Query%' ORDER BY date DESC LIMIT 3;
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE table index name date 4 NULL 72 Using where
Output from Slow Log
# Query_time: 5.545731 Lock_time: 0.000083 Rows_sent: 1 Rows_examined: 5476845 SET timestamp=1360007079; SELECT * FROM table WHERE name LIKE 'The%Query%' ORDER BY date DESC LIMIT 3;
The rows value that is returned from an EXPLAIN is an estimate of the number of rows that have to be examined to find results that match your query.
If you look, you will see that the key being chosen for the query execution is date , which is probably being picked because of your ORDER BY clause. Because the key being used in the query is unrelated to your WHERE clause, that's probably why the estimate is getting messed up. Even though your WHERE clause is doing a LIKE on the name column, the optimizer may decide not to use an index at all:
Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) source
In short, the optimizer is choosing not to use the name key, even though it would be the one that is the limiting factor of rows to be returned. You can try forcing the index to see if that improves the performance.
上一篇: Mysql索引不起作用
下一篇: MySQL解释与慢日志