title: Using a Composite Primary Key across it's full range of values
body: <p>I have a table used to collect timeseries data from thousands of IOT devices every second. This table has a composite primary key to cluster the data for each device to increase the speed of lookups:</p>
  <pre><code>CREATE TABLE 'device_data' (
    device_id int NOT NULL,
    timestamp datetime NOT NULL,
    temperature float NOT NULL,
    PRIMARY KEY (device_id,`time)
  )
  </code></pre>
  <p>I need to delete data older than a year. This can be done efficiently using the primary key on a single device:</p>
  <pre><code>DELETE FROM device_data
    WHERE device_id = 42
    AND timestamp &lt;= CURDATE() - INTERVAL 1 YEAR LIMIT 10000;
  </code></pre>
  <p><strong>How can I do this across the full range of devices using the index?</strong></p>
  <p>I've tried the following query, but it doesn't use the index:</p>
  <pre><code>DELETE FROM device_data WHERE device_id in
    (SELECT device_id FROM device_data GROUP BY device_id)
    AND timestamp &lt;= CURDATE() - INTERVAL 1 YEAR LIMIT 10000;
  </code></pre>
  <p>I have a script that selects the device_id's in batches of 100 and then makes a second delete query with these 100 device_id's hard-coded, efficiently using the index.</p>
  <p>However, I'd really like to know:</p>
  <p><strong>Why can't MySQL use this index across the full range? The data seems to be efficiently ordered and clustered?</strong></p>