Bug #5982 Index is ignored for BETWEEN constraint
Submitted: 8 Oct 2004 14:28 Modified: 17 May 2005 21:08
Reporter: Vassili Gorshkov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:All releases, including 501 OS:Microsoft Windows (Windows, others?)
Assigned to: CPU Architecture:Any
Triage: Triaged: D5 (Feature request)

[8 Oct 2004 14:28] Vassili Gorshkov
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.
[13 Oct 2004 21:04] Matthew Lord
Hi,

Thank you for your bug report.

MySQL does not currently support Dynamic Ranges yet.  I've verified the issue and changed it to 
a feature request.

Best Regards
[14 Jun 2007 15:24] Stephen McCants
Has this feature been added?