hardDesignConceptMidSeniorStaff
A query filters on two columns and sorts on a third. What index do you create, and what does it cost you?
6 min readUpdated 2026-08-15
Answer
Short answer
Put the equality predicates first in a composite index and the ordering column last, so one index serves the filter, the sort and the limit. Every index is a write tax, and whether that tax is a random in-place page update or a sequential append is the difference between a B-tree engine and an LSM engine.
Was this answer helpful?
Likely follow-ups
- The planner ignores your new index and does a sequential scan. Give me three reasons that could be correct.
- What is a covering index, and what stops it from being one after a schema change?
- Why do deletes make range reads slower in an LSM engine but not in a B-tree?
- You need this index built on a 400 GB live table. How do you do it without an outage?
Related questions
- This table has fourteen indexes and writes have got slower. How do you work out which ones to drop?hardAlso on indexing and write-amplification6 min
- A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?hardAlso on indexing5 min
- You need every driver within two kilometres of a moving rider, updated every four seconds. What index makes that cheap?hardAlso on indexing6 min
- Three queries hit the same table with different filters. What composite indexes do you build, and how do you order the columns?hardAlso on indexing6 min