Description:
MySQL avoids ranged scan if there is another possible index, even if ranged scan is more efficient in this case.
EXPLAIN SELECTs:
mysql> desc select count(*) from inc_performance where time_start >= '2005-02-04 16:30:00' AND inc_performance.time_start < '2005-02-15 16:30:00' and intv_type IN (1);
+-----------------+------+----------------------+-----------+---------+-------+-------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------------+------+----------------------+-----------+---------+-------+-------+-------------+
| inc_performance | ref | time_start,intv_type | intv_type | 5 | const | 63286 | Using where |
+-----------------+------+----------------------+-----------+---------+-------+-------+-------------+
1 row in set (0.24 sec)
mysql> select count(*) from inc_performance where time_start >= '2005-02-04 16:30:00' AND inc_performance.time_start < '2005-02-15 16:30:00' and intv_type IN (1);
+----------+
| count(*) |
+----------+
| 3168 |
+----------+
1 row in set (0.32 sec)
mysql> desc select count(*) from inc_performance use index (time_start) where time_start >= '2005-02-04 16:30:00' AND inc_performance.time_start < '2005-02-15 16:30:00' and intv_type IN (1);
+-----------------+-------+---------------+------------+---------+------+-------+-------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------------+-------+---------------+------------+---------+------+-------+-------------+
| inc_performance | range | time_start | time_start | 9 | NULL | 10455 | Using where |
+-----------------+-------+---------------+------------+---------+------+-------+-------------+
1 row in set (0.00 sec)
mysql> select count(*) from inc_performance use index (time_start) where time_start >= '2005-02-04 16:30:00' AND inc_performance.time_start < '2005-02-15 16:30:00' and intv_type IN (1);
+----------+
| count(*) |
+----------+
| 3168 |
+----------+
1 row in set (0.14 sec)
As you can see range for time_start is better than index for intv_type.
How to repeat:
1. restore uploaded table inc_performance.sql
2. run the above queries.
