# Weird behavior of indexes - can someone please explain?
**URL:** https://forums.percona.com/t/weird-behavior-of-indexes-can-someone-please-explain/3076
**Category:** Other MySQL® Questions
**Created:** [November 12, 2013, 3:11pm UTC](https://forums.percona.com/t/weird-behavior-of-indexes-can-someone-please-explain/3076 "2013-11-12T15:11:41Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@JohnG](https://forums.percona.com/u/JohnG)
#### Post date: [November 12, 2013, 3:11pm UTC](https://forums.percona.com/t/weird-behavior-of-indexes-can-someone-please-explain/3076/1 "2013-11-12T15:11:41Z")
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)
1.
* * *
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 |
±—±------------±---------±------±------------------±--------------±--------±-----±-----±------------+
1.
* * *
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
---
### Author:  [@JohnG](https://forums.percona.com/u/JohnG)
#### Post date: [November 13, 2013, 8:40am UTC](https://forums.percona.com/t/weird-behavior-of-indexes-can-someone-please-explain/3076/2 "2013-11-13T08:40:31Z")
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