intermediateIndexes — Clustered, Non-Clustered, Composite, Covering
A query filtering on three columns (status, order_date, customer_id) is slow. How do you design the optimal index?
Analysis steps: 1) Check selectivity: how many distinct values? status (3-5 values, low selectivity), customer_id (1000 values, high selectivity), order_date (365 days, high selectivity). 2) Identify query pattern: WHERE status = 'PENDING' AND order_date > '2024-01-01' AND customer_id = 42. 3) Composite index design: most selective columns first — BUT consider that equality filters (=) should prec
Ready to master this question?
Generate a complete walkthrough — background, the full answer in plain language, a working code example explained line by line, a real-world scenario, common mistakes, and how this same question gets asked in different ways.
Sign in to generate a response