Not the answer you need?
Register and ask your own question!

Weird behavior of indexes - can someone please explain?

JohnGJohnG EntrantInactive User Role Beginner
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

Comments

  • JohnGJohnG Entrant Inactive User Role Beginner
    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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.