Why does MySQL with InnoDB perform a table check when a key exists and you select 70 times as many rows?

Question

I am trying to fix a query performance issue. Here's the expected query plan from the explanation:

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)

This makes sense because an index called tdcol ( KEY tdcol (tdcol)) is used, and about 5M rows should be selected from this query.

However, if I request another 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 scanning will be better, but it is more than 70 times the number of rows to check, so it’s hard for me to believe that scanning a table is better.

In addition, the syntax "USE KEY tdcol" does not change the query plan.

Thanks in advance for any help, and I am more than happy to provide additional questions / answers.

Answer 1

5 ( , ), 350 ( ).

, , -, . tdcol "" ( , , tdcol), , .

, 5 , , 350 , ( , , ).

Answer 2

MySQL . , MySQL , , , . , , , , . ?

, , MySQL . , Innodb .

Answer 3

? min(), avg(), max(), , . , 1 , .

InnoDB. , , , staticsan. B + Tree.

Answer 4

", , ."

True. . , , .

, "", "". , , , , "" .

, MAX ( ), " "?

If this is the case, then the optimizer could conclude that all the lines satisfy the upper limit in any case and may have decided to go differently compared to the case when he should conclude that "oh, there are certain lines, "t also satisfies the upper limit, so I will only use the index to be safe."