[Solved] A composite index on (category, price) was created, but queries filtering only on price are slow. You try this query:SELECT * FROM products WHERE price What is the best fix? | SQL

SQL - Indexes and Query Performance

A composite index on (category, price) was created, but queries filtering only on price are slow. You try this query:

SELECT * FROM products WHERE price < 100;

What is the best fix?

AChange the composite index to (price, category).

BAll of the above.

CRewrite the query to include category in WHERE.

DCreate a separate index on price alone.

Step-by-Step Solution

Solution:

Step 1: Identify index limitation
Composite index on (category, price) cannot optimize queries filtering only on price.
Step 2: Consider fixes
Creating a separate index on price, changing index order, or adding category filter all help.
Final Answer:
All of the above. -> Option B
Quick Check:
Multiple fixes possible for index order issues = A [OK]

Quick Trick: Fix slow queries by adjusting index or query filters [OK]

Common Mistakes:

Master "Indexes and Query Performance" in SQL

9 interactive learning modes - each teaches the same concept differently

More SQL Quizzes

A composite index on (category, price) was created, but queries filtering only on price are slow. You try this query: