Description:
From
http://dev.mysql.com/doc/mysql/en/where-optimizations.html
. Each table index is queried, and the best index is used unless the optimizer believes that it is more efficient to use a table scan
i did not found what "the best index" is, but it should be the index likely to rturn the least amount of rows?
With the sql below the optimizer chooses an"ref" index that it estimates to return twice as many rows as the "range" index (4 vs 2). i don' t know if, and how much of an overhead is expected between range and ref, but if this is reproduced on a larger table the amount of time saved using the "range" is significant.
The first explain use index a, expecting 4 rows
The 2nd shows that index b, estimates 2 rows, for the same condition.
Only on the 3rd, where the range covers just 1 value the optimizer alters it choise
create table t1 ( a int not null, b int not null, index(a), index(b));
insert into t1 values (1,1),(1,2),(1,3),(1,4),(1,5),(2,6),(2,7),(2,8),(2,9),(2,10);
analyze table t1;
explain select * from t1 where a=1 and b>1 and b<4;
explain select * from t1 use index (b) where a=1 and b>1 and b<4;
explain select * from t1 where a=1 and b>1 and b<3;
drop table t1;
I have some large table with 8 millin rows, ref expected 1.2 million, range 0.8 millions. with the optimizer choise of ref, the query takes over 4 times as long)
How to repeat:
mysql> explain select * from t1 where a=1 and b>1 and b<4;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | t1 | ref | a,b | a | 4 | const | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from t1 use index (b) where a=1 and b>1 and b<4;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | b | b | 4 | NULL | 2 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
mysql> explain select * from t1 where a=1 and b>1 and b<3;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | range | a,b | b | 4 | NULL | 1 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
Suggested fix:
review the conditions between range and ref index
