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
This is a Pro chapter
Sign in, then upgrade to Pro or Power to unlock this and the full Databases Mastery library.
A query filtering on three columns (status, order_date, customer_id) is slow. How do you design the optimal index?