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 |
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)
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 |
±—±------------±---------±------±------------------±--------------±--------±-----±-----±------------+
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
