no index use around year change dates

[Ioannis Cherouvim]

Hello

I have a weird case on my queries which have a between datetime clause. When the range overlaps the year change the index is not used. Proof:

create table articles (
id bigint(20) not null auto_increment,
source varchar(15) not null,
createdAt datetime not null,
primary key (id),
key IDX_createdAt (createdat)
) engine=InnoDB



explain select count(*) from articles where createdAt between '2008-11-30' and '2008-12-10';
explain select count(*) from articles where createdAt between '2008-12-30' and '2009-01-10';
explain select count(*) from articles where createdAt between '2009-01-30' and '2009-02-10';
explain select count(*) from articles where createdAt between '2009-02-30' and '2009-03-10';
explain select count(*) from articles where createdAt between '2009-03-30' and '2009-04-10';


The second query is problematic because it covers the year change. Here are the (merged) explain results:
+----+-------------+----------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | articles | range | IDX_createdAt | IDX_createdAt | 8 | NULL | 1 | Using where; Using index |
| 1 | SIMPLE | articles | range | IDX_createdAt | IDX_createdAt | 8 | NULL | 6040 | Using where; Using index |
| 1 | SIMPLE | articles | range | IDX_createdAt | IDX_createdAt | 8 | NULL | 1 | Using where; Using index |
| 1 | SIMPLE | articles | range | IDX_createdAt | IDX_createdAt | 8 | NULL | 1 | Using where; Using index |
| 1 | SIMPLE | articles | range | IDX_createdAt | IDX_createdAt | 8 | NULL | 1 | Using where; Using index |
+----+-------------+----------+-------+---------------+---------------+---------+------+------+--------------------------+


Any idea why this is happening?

This has extra bad effect when I add another field in the where (non indexed):



explain select count(*) from articles where createdAt between '2008-11-30' and '2008-12-10' and source='FOO';
explain select count(*) from articles where createdAt between '2008-12-30' and '2009-01-10' and source='FOO';
explain select count(*) from articles where createdAt between '2009-01-30' and '2009-02-10' and source='FOO';
explain select count(*) from articles where createdAt between '2009-02-30' and '2009-03-10' and source='FOO';
explain select count(*) from articles where createdAt between '2009-03-30' and '2009-04-10' and source='FOO';

+----+-------------+----------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------------+---------+------+------+-------------+
| 1 | SIMPLE | articles | range | IDX_createdAt | IDX_createdAt | 8 | NULL | 1 | Using where |
| 1 | SIMPLE | articles | ALL | IDX_createdAt | NULL | NULL | NULL |14775 | Using where |
| 1 | SIMPLE | articles | range | IDX_createdAt | IDX_createdAt | 8 | NULL | 1 | Using where |
| 1 | SIMPLE | articles | range | IDX_createdAt | IDX_createdAt | 8 | NULL | 1 | Using where |
| 1 | SIMPLE | articles | range | IDX_createdAt | IDX_createdAt | 8 | NULL | 1 | Using where |
+----+-------------+----------+-------+---------------+---------------+---------+------+------+-------------+





thanks