Hi,

I would like to ask a question regarding the way Percona Server does the indexing when using its XtraDB engine.

I have a table with this structure:

CREATE TABLE `event` (
`id` binary(16) NOT NULL,
`agent_ctx` binary(16) NOT NULL,
`timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`tzone` float NOT NULL DEFAULT '0',
`sensor_id` binary(16) DEFAULT NULL,
`interface` varchar(32) NOT NULL,
[...]
`src_host` binary(16) DEFAULT NULL,
`dst_host` binary(16) DEFAULT NULL,
`src_net` binary(16) DEFAULT NULL,
`dst_net` binary(16) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `event_idx` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


The table has some rows stored:

mysql> select count(*) from event;
+----------+
| count(*) |
+----------+
| 5863 |
+----------+
1 row in set (0.00 sec)


If I select rows with a timestmap > '2012-06-06 14:17:40' some rows are retrieved:

mysql> SELECT count(*) FROM event b WHERE b.timestamp > '2012-06-06 14:17:40';
+----------+
| count(*) |
+----------+
| 18 |
+----------+
1 row in set (0.00 sec)


And if I select rows with a timestmap < '2012-06-06 14:17:40' some rows are retrieved too:

SELECT count(*) FROM event b WHERE b.timestamp < '2012-06-06 14:17:40';
+----------+
| count(*) |
+----------+
| 5843 |
+----------+
1 row in set (0.00 sec)


But when running the explain command this queries seem to be different although I suppose that they could use the same index. Why timestamp index isn't used automatically in the second query?

mysql> explain SELECT * FROM event b WHERE b.timestamp > '2012-06-06 14:17:40';
+----+-------------+-------+-------+---------------+-------- ---+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-------- ---+---------+------+------+-------------+
| 1 | SIMPLE | b | range | event_idx | event_idx | 4 | NULL | 18 | Using where |
+----+-------------+-------+-------+---------------+-------- ---+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT * FROM event b WHERE b.timestamp < '2012-06-06 14:17:40';
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------+
| 1 | SIMPLE | b | ALL | event_idx | NULL | NULL | NULL | 5364 | Using where |
+----+-------------+-------+------+---------------+------+-- -------+------+------+-------------+
1 row in set (0.00 sec)

At the end what I would like to do is deleting rows of this table according their timestamp, but I can't force index use in a delete SQL instruction. How can I use the timestamp index automatically if I have to delete rows that match a condition like "b.timestamp < '2012-06-06 14:17:40'"?

Thanks in advance!!