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

[Paul Dugdale]

Hi! I have a table that collects time series data from several thousand devices. Because this table is large, I've use a composite primary key consisting of the 'device_id' and 'timestamp'. This makes sure it's clustered well for our access patterns, and it doesn't need additional indexes keeping storage down.


CREATE TABLE 'device_data' (
device_id int NOT NULL,
timestamp datetime NOT NULL,
temperature float NOT NULL,
PRIMARY KEY (device_id,`time)
)


I'd really like to know why a query using hard coded device_ids can use the index, but using a subquery doesn't:

E.g. Hardcoding uses the index:
DELETE FROM device_data
WHERE device_id in (1,2,3...1000)
AND timestamp <= CURDATE() - INTERVAL 1 YEAR LIMIT 10000;

E.g. Subquery doesn't use the index:
DELETE FROM device_data
WHERE device_id in (SELECT device_id FROM device_data GROUP BY device_id)
AND timestamp <= CURDATE() - INTERVAL 1 YEAR LIMIT 10000;