Re: Why can't MySQL use a range over a Clustered Primary Key

[Paul Dugdale]

Thanks very much!

I'm also on MySQL 8.0.27. Here are the explains of the two queries:

mysql> EXPLAIN SELECT * FROM device_data WHERE device_id IN (1) AND timestamp <= CURDATE() - INTERVAL 1 YEAR;
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | device_data | NULL | range | PRIMARY | PRIMARY | 9 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


mysql> EXPLAIN SELECT * FROM device_data WHERE device_id IN (SELECT device_id FROM device_data GROUP BY device_id) AND timestamp <= CURDATE() - INTERVAL 1 YEAR;
+----+-------------+-------------+------------+------+---------------+---------+---------+------------------------------------------------+------+----------+--------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+---------+---------+------------------------------------------------+------+----------+--------------------------------------+
| 1 | SIMPLE | device_data | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where |
| 1 | SIMPLE | device_data | NULL | ref | PRIMARY | PRIMARY | 4 | controlworks_development.device_data.device_id | 1 | 100.00 | Using index; FirstMatch(device_data) |
+----+-------------+-------------+------------+------+---------------+---------+---------+------------------------------------------------+------+----------+--------------------------------------+
2 rows in set, 1 warning (0.00 sec)


I would have expected MySQL could use the Primary Key as an index in the second query, since the data is structured correctly to do that?