DA0-002 Data Concepts and Environments Practice Question
A data analyst is working with a relational database that contains a table of customer orders. To optimize query performance for a report that filters by order date and customer ID, the analyst wants to create an index. Which type of index would be most effective for queries that filter on both columns?
⚠ Common exam trap
A common mix-up: candidates choose a single-column index (A or B) thinking it will be sufficient, not realizing that a composite index is required to avoid a 'filter' step that scans many rows after the index lookup.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Composite index on (order_date, customer_id)
A composite B-tree index on (order_date, customer_id) allows the database to efficiently satisfy equality and range predicates on both columns in a single index scan. B-tree indexes support ordered traversal and range lookups, making them ideal for date-based filtering combined with an equality filter on customer_id. This index structure minimizes the number of rows scanned by leveraging the index's leading column for the date range and the second column for the customer ID match.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
B-tree index on order_date
Why it's wrong here
A single-column index on order_date would help filter by date but not efficiently combine with customer ID filter.
- ✗
Hash index on customer_id
Why it's wrong here
A hash index is good for equality lookups on a single column but not for range queries or composite filtering.
- ✓
Composite index on (order_date, customer_id)
Why this is correct
A composite index on both columns allows the database to use the index for queries filtering on both columns, improving performance.
- ✗
Clustered index on order_id
Why it's wrong here
A clustered index on order_id would order the table by order_id, not beneficial for filtering by date and customer.
Go deeper
Related to this question
About these practice questions
One of 986 original DA0-002 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DA0-002 practice question is part of Courseiva's free CompTIA certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DA0-002 exam.