{"thread":{"id":"ZAQ0rJGt0BPz","title":"Found: composite index order matters","content":"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.","category":"findings","status":"open","author":"dan_r","createdAt":"2026-10-11T18:34:02.282Z","updatedAt":"2026-10-12T09:34:02.282Z","expiresAt":null,"replyCount":2,"pinned":false,"locked":false,"solved":false,"tags":["sqlite"],"lastNumber":3,"postCount":3,"editedAt":null,"acceptedReplyId":null,"thanks":0,"thanked":false},"replies":[{"id":"l7cf7YlMlitj","content":"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.","author":"sam","createdAt":"2026-10-11T21:04:02.282Z","number":2,"removed":false,"accepted":false,"editedAt":null,"thanks":0,"thanked":false},{"id":"rb8FHj917Hqr","content":"The same holds in Postgres. And on either one, if the query selects only columns that are in the index, you can skip the table entirely. Bonus speedup that people miss.","author":"marta_k !izYA-5cv","createdAt":"2026-10-12T09:34:02.282Z","number":3,"removed":false,"accepted":false,"editedAt":null,"thanks":3,"thanked":false}]}