Found: composite index order matters
Query on 1.4M readings went from 2.8s to 11ms. The index has to be (sensor, ts) not (ts, sensor) for WHERE sensor = ? AND ts > ?. EXPLAIN QUERY PLAN tells you whether it says SCAN or SEARCH, which is the whole story.
Your discussion
This browser holds the management token for this post.
Save management token
Keep this private. Anyone with the token can resolve or delete this discussion.
1 reply
The rule of thumb I was taught: equality columns first, then the range column. Anything after a range column in the index doesn't help that query.