DBS-C01 Workload-Specific Database Design Practice Question
A company uses Amazon RDS for MySQL with Multi-AZ and read replicas. The database has a table storing user sessions with 50 million rows. The application team reports that queries using 'SELECT * FROM sessions WHERE user_id = ? ORDER BY login_time DESC LIMIT 10' are slow. The EXPLAIN plan shows a full table scan. Which design change would BEST improve query performance?
⚠ Common exam trap
The DBS-C01 exam often tests the misconception that adding more resources (Option D) or partitioning (Option C) can substitute for proper indexing, when in fact the most efficient fix for a query with a WHERE and ORDER BY on different columns is a composite index that covers both.
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 composite index on (user_id, login_time)
The query filters on user_id and orders by login_time, which is a classic case for a composite index. A B-Tree index on (user_id, login_time) allows MySQL to locate all rows for the given user_id via the index's leading column and then retrieve the rows in sorted order using the second column, avoiding a full table scan and a filesort operation. This directly addresses the root cause — the lack of an index to support both the WHERE and ORDER BY clauses efficiently.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Implement an application-level cache using ElastiCache
Why it's wrong here
Caching reduces load but does not fix the inefficient query.
- ✓
Create a composite index on (user_id, login_time)
Why this is correct
This index covers both the WHERE and ORDER BY clauses.
- ✗
Partition the table by user_id
Why it's wrong here
Partitioning the table by `user_id` would facilitate partition pruning, reducing the data scanned for the `WHERE user_id = ?` clause. However, it does not create the necessary index to efficiently locate and then sort the top 10 rows by `login_time DESC`, which is the core reason for the reported full table scan and slow performance. Partitioning is typically used for managing very large tables, improving query performance by limiting the search space, or for maintenance tasks like archiving old data, but it does not substitute for a suitable index to accelerate `ORDER BY` and `LIMIT` clauses.
- ✗
Upgrade to a larger instance type with more memory
Why it's wrong here
More memory may help caching but does not eliminate the full table scan.
Go deeper
Related to this question
About these practice questions
One of 1,663 original DBS-C01 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 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.