Details
-
Task
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
CREATE TABLE tbl (
|
...
|
 |
PRIMARY KEY (pk),
|
KEY key1 (key1,pk)
|
) ENGINE=InnoDB;
|
And two query plans:
# The bad one:
|
mysql> explain SELECT * FROM tbl1 WHERE key1 = const1 AND pk > const2A limit 1,1;
|
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
|
| 1 | SIMPLE | tbl1 | ref | PRIMARY,key1 | key1 | 9 | const | 24398 | Using where |
|
+----+-------------+-------+------+---------------+------+---------+-------+-------+-------------+
|
# The good one one:
|
mysql> explain SELECT * FROM tbl1 WHERE key1 = const1 AND pk > const2B limit 1,1;
|
+----+-------------+-------+-------+----------------+------+---------+------+---------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+----+-------------+-------+-------+----------------+------+---------+------+---------+-------------+
|
| 1 | SIMPLE | tbl1 | range | PRIMARY,key1 | key1 | 17 | NULL | 1052604 | Using where |
|
+----+-------------+-------+-------+-------------- -+------+---------+------+---------+-------------+
|
optimizer's choice between Good and Bad query plans depends on the values of const2A and const2B.
Note that both query plans use the same index, but the Good plan uses more key
parts. The point is that Good plan should always be used, because it scans a
proper subset of rows that the Bad plan is scanning.