hardConceptDesignMidSeniorStaff
Three queries hit the same table with different filters. What composite indexes do you build, and how do you order the columns?
6 min readUpdated 2026-08-15
Answer
Short answer
Order by how the predicate uses the column, not by cardinality: equality columns first, then one range column, then columns needed only for sorting.A range predicate stops the index seeking on everything after it, and the leftmost prefix decides which other queries the index can serve.
Was this answer helpful?
Likely follow-ups
- Which of your indexes is now redundant, and how would you prove it before dropping it?
- When would you use a partial index instead of adding a column to an existing one?
- How does an InnoDB secondary index differ from a PostgreSQL one in what it stores?
- The query filters on a low-cardinality flag where 2% of rows are true. What is the best index?
Related questions
- 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 indexing and postgresql5 min
- Before you tune anything, how do you decide which queries are worth your time?mediumAlso on postgresql and mysql4 min
- This table has fourteen indexes and writes have got slower. How do you work out which ones to drop?hardAlso on indexing and postgresql6 min
- A query has an index on the filtered column but the plan shows a sequential scan. Walk me through how you would diagnose it.hardAlso on indexing and postgresql3 min