DBS-C01 Workload-Specific Database Design Practice Question
A company uses Amazon Aurora MySQL for its customer relationship management (CRM) system. The database has a table "contacts" with millions of rows. The application frequently searches for contacts by email address. The email column has a B-tree index. The DBA notices that queries are still slow, and the EXPLAIN plan shows index scans but not index-only scans. What is the most likely cause?
⚠ Common exam trap
Test-takers frequently assume any index scan is optimal, failing to recognize that an index-only scan (covered index) is significantly faster because it avoids table row lookups, and the EXPLAIN plan's 'Using index' vs. 'Using index condition' distinction is the key clue.
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 columns not included in the index, requiring table lookups.
An index-only scan requires that all columns referenced in the query (both in the SELECT list and WHERE clause) be present in the index. Since the query selects columns not included in the B-tree index on the email column, Aurora MySQL must perform additional table lookups (row fetches) to retrieve those missing columns, resulting in an index scan rather than an index-only scan.
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 query selects columns not included in the index, requiring table lookups.
Why this is correct
If the query selects columns like phone not in the index, the database must access the table, preventing an index-only scan.
- ✗
The index is a composite index on (email, phone) and the query selects only email.
Why it's wrong here
A composite index on (email, phone) would still be used for email-only queries, but it may be larger.
- ✗
The index has low cardinality.
Why it's wrong here
Low cardinality would cause many rows per key, but that leads to index scans, not index-only scans.
- ✗
The index type is not suitable for equality searches.
Why it's wrong here
B-tree indexes are excellent for equality searches.
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.