SQL - Indexes and Query Performance
Given a composite index on
(city, age) in a table users, which query will use the index efficiently?Given a composite index on (city, age) in a table users, which query will use the index efficiently?
medium📝 query result Q13 of 15
Step-by-Step Solution
Solution:
Step 1: Understand index column order usage
A composite index on (city, age) can be used efficiently if the query filters on city first, then age.Step 2: Analyze each query
SELECT * FROM users WHERE city = 'Boston' AND age > 30; filters on city and age, matching index order. SELECT * FROM users WHERE age > 30; filters only on age (second column), so index is less useful. SELECT * FROM users WHERE age = 25 AND city = 'Boston'; filters age first, then city, which is less efficient. SELECT * FROM users WHERE city = 'Boston' ORDER BY age DESC; filters city but only orders by age, which may use index partially.Final Answer:
SELECT * FROM users WHERE city = 'Boston' AND age > 30; -> Option CQuick Check:
Index (city, age) best for city then age filters [OK]
Quick Trick: Filter on first index column before others [OK]
Common Mistakes:
- Assuming index works well if filtering only second column
- Ignoring column order in composite index
- Thinking ORDER BY always uses index fully
Master "Indexes and Query Performance" in SQL
Want More Practice?
15+ quiz questions · All difficulty levels · Free
Free Signup - Practice All QuestionsMore SQL Quizzes