A query filtering on two columns (`country` and `city`) is slow. Which indexing strategy would likely provide the best performance improvement?
Go & Rust interview question for Advanced practice.
Answer
Create a composite index on (country, city).
Explanation
For a query that filters on multiple columns with AND, a composite index on those columns is typically the most efficient solution. A composite index on (country, city) allows the database to efficiently locate all rows matching both the country and the city in a single index structure. While having two separate indexes might work (the database could perform an index merge), it's usually less efficient than a single, tailored composite index.