Given a composite index on (country, city) in a table locations, what will be the result of this query?

medium📝 query result Q4 of 15

SQL - Indexes and Query Performance

Given a composite index on (country, city) in a table locations, what will be the result of this query?

SELECT * FROM locations WHERE city = 'Paris';

AThe query will use the composite index efficiently.

BThe query will perform a full table scan.

CThe query will use only the city part of the index.

DThe query will cause a syntax error.

Step-by-Step Solution

Solution:

Step 1: Understand index column order
The composite index is on (country, city), so it is sorted first by country.
Step 2: Analyze query filter
The query filters only on city, skipping the first column country, so index cannot be used efficiently.
Final Answer:
The query will perform a full table scan. -> Option B
Quick Check:
Index on (country, city) can't optimize city-only filter = B [OK]

Quick Trick: Index first column must be in WHERE for index use [OK]

Common Mistakes:

Master "Indexes and Query Performance" in SQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free

More SQL Quizzes