You want to speed up queries that filter by both category and price columns in the items table. Which is the best way to create an index?
hard📝 Application Q15 of 15
SQL - Indexes and Query Performance
You want to speed up queries that filter by both category and price columns in the items table. Which is the best way to create an index?
ACREATE INDEX idx_cat_price ON items (category, price);
BCREATE INDEX idx_cat ON items (category); CREATE INDEX idx_price ON items (price);
CCREATE INDEX idx_price_cat ON items (price, category);
DCREATE INDEX idx_items ON items (category);
Step-by-Step Solution
Solution:
Step 1: Understand multi-column index usage
A single index on (category, price) helps queries filtering by both columns efficiently.
Step 2: Compare options
CREATE INDEX idx_cat_price ON items (category, price); creates a composite index on category and price in that order, which is best for combined filters.
Final Answer:
CREATE INDEX idx_cat_price ON items (category, price); -> Option A
Quick Check:
Composite index on both columns is best [OK]
Quick Trick:Use composite index for multiple columns searched together [OK]
Common Mistakes:
Creating separate indexes instead of composite
Wrong column order in composite index
Creating index on only one column when filtering by two
Master "Indexes and Query Performance" in SQL
9 interactive learning modes - each teaches the same concept differently
