DBS-C01 Workload-Specific Database Design Practice Question
A company uses Amazon RDS for MySQL to store e-commerce order data. The orders table has millions of rows and is frequently queried by order_id. The company also runs periodic reports that aggregate data by order_date. The reports are slow. The database has a primary key on order_id. The company needs to improve report performance without affecting OLTP queries. Which design change should be made?
⚠ Common exam trap
Many candidates assume a read replica (Option C) solves all performance issues, but without an appropriate index, the replica still performs full table scans, making the reports slow regardless of where they run.
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
✓
Create a secondary index on order_date.
Creating a secondary index on order_date allows MySQL to quickly locate rows matching the report's date range without scanning the entire table, significantly improving aggregation performance. This index is separate from the primary key on order_id, so OLTP queries that filter by order_id remain unaffected. The index provides a balanced approach: it accelerates read-heavy reporting while adding minimal overhead to write operations.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Create a secondary index on order_date.
Why this is correct
A secondary index on order_date speeds up date-based aggregations without impacting OLTP queries.
- ✗
Upgrade to a larger instance type.
Why it's wrong here
Hardware upgrade may help but is not a targeted optimization.
- ✗
Create a read replica and run reports on the replica.
Why it's wrong here
This offloads reporting but does not improve the performance of the queries themselves.
- ✗
Partition the table by order_date.
Why it's wrong here
Partitioning can help with data management but may not improve aggregation queries without proper indexing.
Go deeper
Related to this question
About these practice questions
This DBS-C01 question is part of Courseiva's 1,663-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.