Details
-
Task
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
We are seeing where a seemingly simple query is not using the correct index, and instead preferring an full table scan, which is resulting in a slower query.
Here is a simplified version of the CREATE TABLE and SELECT:
CREATE TABLE `t1` (
|
`ts` datetime NOT NULL DEFAULT current_timestamp(),
|
`val1` varchar(5) NOT NULL DEFAULT '0',
|
`val2` varchar(3) NOT NULL DEFAULT '0',
|
KEY `idx_val1_val2` (`val1`,`val2`),
|
KEY `idx_ts` (`ts`)
|
) ENGINE=InnoDB;
|
SELECT ts, val1, val2 FROM t1 WHERE val1='04643' AND val2='955' ORDER BY ts DESC LIMIT 1;
|
If I run this through EXPLAIN with 0 data, I see:
mysql> EXPLAIN SELECT ts, val1, val2 FROM t1 WHERE val1='04643' AND val2='955' ORDER BY ts LIMIT 1;
|
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | index | idx_val1_val2 | idx_ts | 5 | NULL | 1 | Using where |
|
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|
If I add some rows, I still see the same:
INSERT INTO t1 VALUES (now(),'04643','955');
|
INSERT INTO t1 VALUES (now(),'04643','999');
|
INSERT INTO t1 VALUES (now(),'04643','955');
|
INSERT INTO t1 VALUES (now(),'04643','999');
|
INSERT INTO t1 VALUES (now(),'04643','955');
|
INSERT INTO t1 VALUES (now(),'04643','999');
|
INSERT INTO t1 VALUES (now(),'04643','955');
|
INSERT INTO t1 VALUES (now(),'04643','999');
|
INSERT INTO t1 VALUES (now(),'04643','955');
|
INSERT INTO t1 VALUES (now(),'04643','999');
|
mysql> EXPLAIN SELECT ts, val1, val2 FROM t1 WHERE val1='04643' AND val2='955' ORDER BY ts DESC LIMIT 1;
|
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|
| 1 | SIMPLE | t1 | index | idx_val1_val2 | idx_ts | 5 | NULL | 2 | Using where |
|
+------+-------------+-------+-------+---------------+--------+---------+------+------+-------------+
|
If I remove the "LIMIT 1", then it chooses the correct index.
Note that in the above EXPLAIN, only 1 index is listed for "possible_keys", yet the other is chosen for "key", so this tells me the full table scan was preferred over the index, and then the secondary index was used for the ORDER BY.
This incorrect choice leads to slower times as opposed to when it chooses the correct index (or FORCE INDEX is used).
Attachments
Issue Links
- relates to
-
-
- Open
-