[Solved] You have a large table orders with columns order_id, customer_id, and order_date.... How should you create an index to optimize these queries? | SQL

SQL - Indexes and Query Performance

You have a large table orders with columns order_id, customer_id, and order_date. You often query orders by customer_id and sort by order_date. How should you create an index to optimize these queries?

ACreate a single-column B-tree index on <code>order_date</code>

BCreate separate B-tree indexes on <code>customer_id</code> and <code>order_date</code>

CCreate a hash index on <code>customer_id</code>

DCreate a composite B-tree index on (<code>customer_id</code>, <code>order_date</code>)

Step-by-Step Solution

Solution:

Step 1: Understand query pattern
Queries filter by customer_id and sort by order_date, so both columns matter together.
Step 2: Choose index type for filtering and sorting
A composite B-tree index on (customer_id, order_date) supports filtering by customer and sorting by date efficiently.
Final Answer:
Create a composite B-tree index on (customer_id, order_date) -> Option D
Quick Check:
Composite index matches filter + sort columns [OK]

Quick Trick: Composite indexes speed up queries filtering and sorting on multiple columns [OK]

Common Mistakes:

Creating index only on sort column
Using separate indexes instead of composite
Choosing hash index which doesn't support sorting

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

More SQL Quizzes

How should you create an index to optimize these queries?

Step 1: Understand query pattern

Step 2: Choose index type for filtering and sorting

Final Answer:

Quick Check:

Want More Practice?