GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Doubt about indexing

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?

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

  • #2
    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

    Comment

    Working...
    X