DBS-C01 Workload-Specific Database Design Practice Question
A media company stores video metadata in Amazon Aurora MySQL. The application performs frequent range queries on a 'creation_date' column. The table has 10 million rows. The team notices that queries filtering on 'creation_date' are slow despite an index on that column. The query pattern is: SELECT * FROM videos WHERE creation_date BETWEEN '2023-01-01' AND '2023-01-31' ORDER BY creation_date LIMIT 100. The execution plan shows a full index scan. What is the MOST likely cause?
⚠ Common exam trap
A common mix-up: candidates assume an index is not being used (Option C) when the execution plan shows a full index scan, but the real issue is the overhead of retrieving all columns from the table, which is a common performance pitfall with SELECT * queries.
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
✓
The query selects all columns, causing excessive table access
The query uses SELECT *, which forces the database engine to retrieve all columns from the table. Even though the index on creation_date is used for sorting and filtering, the query optimizer may choose a full index scan because it still needs to access the table rows for the non-indexed columns. This is often more efficient than random lookups for a large range, but it still results in scanning many index entries and performing table lookups, causing the observed slowness.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
The index should be a composite index on (creation_date, id)
Why it's wrong here
A composite index might help but won't solve the SELECT * problem.
- ✗
The table is not partitioned by creation_date
Why it's wrong here
Partitioning could improve performance but is not the root cause here.
- ✗
The index on creation_date is not being used
Why it's wrong here
The execution plan shows a full index scan, so the index is used.
- ✓
The query selects all columns, causing excessive table access
Why this is correct
SELECT * forces the database to fetch full rows; a covering index could avoid that.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DBS-C01 question from scratch — 1,663 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DBS-C01 practice question is part of Courseiva's free Amazon Web Services 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 DBS-C01 exam.