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