Mysql does not use index, even if example is trivial?
Posted by:
L P
Date: July 17, 2009 05:16AM
Hej,
we got this trivial example:
create table t (id int, ts timestamp, data blob) type=Innodb;
alter table t add index TS(ts);
insert into t values(1,now(),'foo');
insert into t values(2,now(),'foo');
insert into t values(3,now(),'foo');
insert into t values(4,now(),'foo');
insert into t values(5,now(),'foo');
insert into t values(6,now(),'foo');
Data now looks like this:
+------+---------------------+------+
| id | ts | data |
+------+---------------------+------+
| 1 | 2009-07-17 13:09:57 | foo |
| 2 | 2009-07-17 13:10:04 | foo |
| 3 | 2009-07-17 13:10:08 | foo |
| 4 | 2009-07-17 13:10:11 | foo |
| 5 | 2009-07-17 13:10:15 | foo |
| 6 | 2009-07-17 13:10:18 | foo |
+------+---------------------+------+
But the index is only used, if the we compare the value with ">", not with "<":
mysql> explain select * from t where ts > '2009-07-17 13:10:11';
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | range | TS | TS | 4 | NULL | 2 | Using where |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
mysql> explain select * from t where ts < '2009-07-17 13:10:11';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | ALL | TS | NULL | NULL | NULL | 6 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
This is really strange - what's so special about one comparison operator as compared to the other? :-/
Any help appreciated...