Description:
In the following select statment only the first field (f1) of a 2 field index is used:
SELECT * from R,T WHERE f1 = r0 AND f2 BETWEEN r1 AND r2;
mysql> EXPLAIN SELECT * from R,T WHERE f1 = r0 AND f2 BETWEEN r1 AND r2;
+----+-------------+-------+------+---------------+------+---------+----------+-
-----+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+------+---------------+------+---------+----------+-
-----+--------------------------+
| 1 | SIMPLE | R | ALL | NULL | NULL | NULL | NULL |
2 | |
| 1 | SIMPLE | T | ref | i1 | i1 | 5 | vvg.R.r0 |
2 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+----------+-
-----+--------------------------+
2 rows in set (0.00 sec)
This makes our queries run at 1/100 speed of what they should have.
How to repeat:
To reproduce:
CREATE TABLE R(
r0 INT,
r1 INT,
r2 INT);
INSERT INTO R VALUES (1,5,7), (2,3,9);
CREATE TABLE T(
f1 INT,
f2 INT);
CREATE INDEX i1 ON T(f1,f2);
INSERT INTO T VALUES
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,7),
(1,8),
(1,9),
(2,1),
(2,2),
(2,3),
(2,4),
(2,5),
(2,6),
(2,7),
(2,8),
(2,9);
EXPLAIN SELECT * from R,T WHERE f1 = r0 AND f2 BETWEEN r1 AND r2;
Suggested fix:
Make sure that you use *all* the fields available when computing the value of an index. In this case the search should have started with looking up the pair (r0,r1) in index i1, then iterate over all the values of i1 until (r0,r2) is reached or passed.
