Why is MySQL with InnoDB doing a table scan when key exists and choosing to examine 70 times more ro

Posted by andysk on Stack Overflow See other posts from Stack Overflow or by andysk
Published on 2010-04-15T01:13:11Z Indexed on 2010/04/15 1:23 UTC
Read the original article Hit count: 354

Filed under:

mysql

|

innodb

|

Performance

|

optimization

|

database

Hello, I'm troubleshooting a query performance problem. Here's an expected query plan from explain:

mysql> explain select * from table1 where tdcol between '2010-04-13:00:00' and '2010-04-14 03:16';
+----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+
| id | select_type | table              | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+
|  1 | SIMPLE      | table1             | range | tdcol         | tdcol        | 8       | NULL | 5437848 | Using where | 
+----+-------------+--------------------+-------+---------------+--------------+---------+------+---------+-------------+
1 row in set (0.00 sec)

That makes sense, since the index named tdcol (KEY tdcol (tdcol)) is used, and about 5M rows should be selected from this query.

However, if I query for just one more minute of data, we get this query plan:

mysql> explain select * from table1 where tdcol between '2010-04-13 00:00' and '2010-04-14 03:17';
+----+-------------+--------------------+------+---------------+------+---------+------+-----------+-------------+
| id | select_type | table              | type | possible_keys | key  | key_len | ref  | rows      | Extra       |
+----+-------------+--------------------+------+---------------+------+---------+------+-----------+-------------+
|  1 | SIMPLE      | table1             | ALL  | tdcol         | NULL | NULL    | NULL | 381601300 | Using where | 
+----+-------------+--------------------+------+---------------+------+---------+------+-----------+-------------+
1 row in set (0.00 sec)

The optimizer believes that the scan will be better, but it's over 70x more rows to examine, so I have a hard time believing that the table scan is better.

Also, the 'USE KEY tdcol' syntax does not change the query plan.

Thanks in advance for any help, and I'm more than happy to provide more info/answer questions.

Developer IT