Bird
0
0

You want to speed up queries filtering by city and state. Which B-tree index is best?

hard📝 Application Q8 of 15
SQL - Indexes and Query Performance
You want to speed up queries filtering by city and state. Which B-tree index is best?
AA composite B-tree index on (state, city).
BSeparate B-tree indexes on city and state.
CA hash index on city only.
DNo index; rely on full table scans.
Step-by-Step Solution
Solution:

  1. Step 1: Understand composite indexes

    A composite index on (state, city) efficiently supports queries filtering by both columns.

  2. Step 2: Compare with other options

    Separate indexes are less efficient for combined filters; hash indexes don't support range queries; no index slows queries.

  3. Final Answer:

    A composite B-tree index on (state, city). -> Option A

  4. Quick Check:

    Composite indexes speed multi-column filters [OK]
Quick Trick: Use composite indexes for multi-column filters [OK]
Common Mistakes:
  • Using separate indexes instead of composite
  • Choosing hash index for range queries
  • Ignoring indexes and scanning full table

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions
More SQL Quizzes
Advanced Query Patterns - Running total without window functions - Quiz 1easy Advanced Query Patterns - Top-N per group query - Quiz 2easy Indexes and Query Performance - EXPLAIN plan for query analysis - Quiz 7medium SQL Security Basics - Parameter binding mental model - Quiz 8hard Stored Procedures and Functions - User-defined functions - Quiz 11easy Stored Procedures and Functions - User-defined functions - Quiz 13medium Stored Procedures and Functions - Parameters (IN, OUT, INOUT) - Quiz 14medium Transactions and Data Integrity - COMMIT and ROLLBACK behavior - Quiz 4medium Window Functions Fundamentals - Why window functions are needed - Quiz 5medium Window Functions Fundamentals - NTILE for distribution - Quiz 3easy