MySQL Forums
Forum List  »  InnoDB

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...

Options: ReplyQuote


Subject
Views
Written By
Posted
Mysql does not use index, even if example is trivial?
4335
L P
July 17, 2009 05:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.