Consider a table sales(region VARCHAR, sale_date DATE, amount DECIMAL). You want to create an index to optimize queries filtering by region and sorting by sale_date. Which statement is best?

hard📝 Application Q9 of 15

SQL - Indexes and Query Performance

Consider a table sales(region VARCHAR, sale_date DATE, amount DECIMAL). You want to create an index to optimize queries filtering by region and sorting by sale_date. Which statement is best?

ACREATE INDEX idx_region_date ON sales(region, sale_date);

BCREATE INDEX idx_date_region ON sales(sale_date, region);

CCREATE UNIQUE INDEX idx_region_date ON sales(region, sale_date);

DCREATE INDEX idx_region ON sales(region);

Step-by-Step Solution

Solution:

Step 1: Understand index order importance
Index columns order matters; filtering column should come first, then sorting column.
Step 2: Choose index matching filtering and sorting
CREATE INDEX idx_region_date ON sales(region, sale_date); indexes region first (filter), then sale_date (sort), optimizing both.
Final Answer:
CREATE INDEX idx_region_date ON sales(region, sale_date); -> Option A
Quick Check:
Index column order matches query needs [OK]

Quick Trick: Put filter columns before sort columns in multi-column index [OK]

Common Mistakes:

Reversing column order in index
Using UNIQUE when not needed
Creating index on only one column

Master "Indexes and Query Performance" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

Want More Practice?

15+ quiz questions · All difficulty levels · Free

Free Signup - Practice All Questions

More SQL Quizzes

Consider a table sales(region VARCHAR, sale_date DATE, amount DECIMAL). You want to create an index to optimize queries filtering by region and sorting by sale_date. Which statement is best?

Step 1: Understand index order importance

Step 2: Choose index matching filtering and sorting

Final Answer:

Quick Check:

Want More Practice?