Topic: Database Management Systems
Consider a table users(userid, country, city, street) with 50 million users. DBA creates the following index for the table
CREATE INDEX myindex ON users(country, city, street)
For which of the following queries will this index be least useful?
- A. SELECT userid FROM users WHERE country='IN'
- B. SELECT userid FROM users WHERE street='MG Road'
- C. SELECT userid FROM users WHERE country='IN' AND city='Pune' AND street='MG Road'
- D. SELECT userid FROM users WHERE country='IN' AND city='Pune'
Answer: B. SELECT userid FROM users WHERE street='MG Road'
Explanation: A composite index (e.g., on A, B, C) works effectively only if the query uses the leading columns of the index. It can resolve queries filtering by (A), by (A and B), or by (A, B, and C). Option (b) filters entirely by the third column (`street`) without referencing the leading column (`country`), rendering the composite B-tree index essentially useless for range scans or quick lookups, resulting in a full table scan.