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 B-tree index on order_date supports date predicates but leaves customer_id unindexed, forcing lookups on the remaining filter. It is tempting because B-trees handle range queries well, and it would be correct if the report filtered on order_date alone.
- ✗
Hash index on customer_id
Why it's wrong here
A hash index on customer_id supports only equality lookups on that column and cannot serve range predicates on order_date. It is tempting because hash indexes are fast for exact matches, and it would be correct for queries filtering solely by customer_id equality.
- ✓
Composite index on (order_date, customer_id)
Why this is correct
A composite index stores the two key columns together in a defined order, so the database can satisfy the combined order_date and customer_id filter from a single index structure rather than intersecting separate single-column indexes or scanning the table.
- ✗
Clustered index on order_id
Why it's wrong here
A clustered index on order_id orders rows physically by that column, so predicates on order_date and customer_id cannot seek. It is tempting because clustering suits range scans, and it would be correct for reports filtering or sorting primarily by order_id.
Go deeper
Related to this question
About these practice questions
One of 1,004 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.