Doubt about indexing

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?

[B]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)[/B]

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

I opened a similar topic few days ago
http:// forum.percona.com/index.php?t=msg&th=2487&start=0&am p;
the workaround that works for me is casting string as datetime (examples in topic)…maybe in your case casting it as timestamp