Announcement

Announcement Module
Collapse
No announcement yet.

Weird behavior of indexes - can someone please explain?

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

  • Weird behavior of indexes - can someone please explain?

    Hi all,

    We're on Percona's Mysql 5.1.71-rel14.9-log.
    I would appreciate if someone can explain to me how a query that returns 0 rows takes so much longer than a query that returns x amount of rows?

    I'm under the impression that if the index doesn't have any entries that satisfies the where clause it does a full table scan....
    Any help would be much appreciated...


    The table has approximately 200 Million rows.

    Table in question:
    CREATE TABLE `session` (
    `session_id` char(56) NOT NULL,
    `uid` int(10) unsigned DEFAULT NULL,
    `data` mediumblob,
    `time_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `time_modified` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`session_id`),
    KEY `uid` (`uid`),
    KEY `time_modified` (`time_modified`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

    1. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    mysql> select count(*) from session where time_modified >='2012-03-11 02:08:00' and time_modified < '2012-03-11 02:11:00' and uid is null;
    +----------+
    | count(*) |
    +----------+
    | 0 |
    +----------+
    1 row in set, 2 warnings (29 min 52.87 sec)

    mysql> Explain select count(*) from sessions where time_modified >='2012-03-11 02:08:00' and time_modified < '2012-03-11 02:11:00' and uid is null;
    +----+-------------+----------+------+-------------------+------+---------+-------+----------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+----------+------+-------------------+------+---------+-------+----------+-------------+
    | 1 | SIMPLE | session | ref | uid,time_modified | uid | 5 | const | 51695647 | Using where |
    +----+-------------+----------+------+-------------------+------+---------+-------+----------+-------------+
    2. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    mysql> select count(*) from session where time_modified >='2012-03-11 02:11:00' and time_modified < '2012-03-11 02:14:00' and uid is null;
    +----------+
    | count(*) |
    +----------+
    | 0 |
    +----------+
    1 row in set, 2 warnings (24 min 51.47 sec)
    +----+-------------+----------+------+-------------------+------+---------+-------+----------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+----------+------+-------------------+------+---------+-------+----------+-------------+
    | 1 | SIMPLE | session | ref | uid,time_modified | uid | 5 | const | 51695647 | Using where |
    +----+-------------+----------+------+-------------------+------+---------+-------+----------+-------------+
    1 row in set, 2 warnings (0.00 sec)

    3. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    mysql> select count(*) from session where time_modified >='2012-04-11 02:11:00' and time_modified < '2012-04-11 02:14:00' and uid is null;
    +----------+
    | count(*) |
    +----------+
    | 248 |
    +----------+
    1 row in set (0.25 sec)

    mysql> explain select count(*) from session where time_modified >='2012-04-11 02:11:00' and time_modified < '2012-04-11 02:14:00' and uid is null;
    +----+-------------+----------+-------+-------------------+---------------+---------+------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+----------+-------+-------------------+---------------+---------+------+------+-------------+
    | 1 | SIMPLE | session | range | uid,time_modified | time_modified | 5 | NULL | 267 | Using where |
    +----+-------------+----------+-------+-------------------+---------------+---------+------+------+-------------+

    4. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    mysql> select count(*) from session where time_modified >='2012-04-11 02:11:00' and time_modified < '2012-04-11 02:14:00' ;
    +----------+
    | count(*) |
    +----------+
    | 269 |
    +----------+
    1 row in set (0.00 sec)

    mysql> explain select count(*) from sessions where time_modified >='2012-04-11 02:11:00' and time_modified < '2012-04-11 02:14:00';
    +----+-------------+----------+-------+---------------+---------------+---------+------+------+--------------------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+----------+-------+---------------+---------------+---------+------+------+--------------------------+
    | 1 | SIMPLE | session | range | time_modified | time_modified | 5 | NULL | 267 | Using where; Using index |
    +----+-------------+----------+-------+---------------+---------------+---------+------+------+--------------------------+
    1 row in set (0.00 sec)


    Thank you
    JG


  • #2
    For those of you interested... We figured it out.

    The 2 warnings that show are showing in points 1 and 2 are invalid dates. Since we are set as EDT and the field in question is a timestamp, on march 11, 2012 at 2am the time was moved forward to 3am. This makes the date times between 2 and 2:59 am invalid. so the query is doing a full table scan on invalid dates.

    I hope this makes sense and helps someone.... ;-)

    Thanks
    JG

    Comment

    Working...
    X