CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is investigating a report of slow data retrieval for a specific table containing millions of records. Users frequently query this table based on a combination of `order_date` and `customer_id`. An `EXPLAIN` plan reveals that queries are performing full table scans. Which of the following actions would MOST effectively improve the query performance for this scenario?
- AAdding a composite index on (`order_date`, `customer_id`).
- BAdding a clustered index on `customer_id`.
- CAdding a non-clustered index on `order_date`.
- DIncreasing the database transaction log size.
Show answer & explanationAnswer & explanation
Correct answer: A. Adding a composite index on (`order_date`, `customer_id`).
Creating a composite index on both `order_date` and `customer_id` allows the database to efficiently locate records based on both columns used in the WHERE clause, significantly reducing full table scans for queries that use both.
Why the other options are wrong
- B. A clustered index on `customer_id` would reorder the physical storage of the table by customer ID. While it could help queries primarily by `customer_id`, it might not be optimal for combined queries with `order_date` and could even negatively impact other queries if `customer_id` is not the most frequently used primary access path.
- C. An index on `order_date` alone would help for queries filtering only by date, but queries using both `order_date` and `customer_id` would still likely perform a partial scan or full scan for the second condition.
- D. Increasing the transaction log size primarily affects write performance and recovery, not read performance for select queries involving full table scans.
Composite Index
An index created on multiple columns of a table, allowing the database to efficiently search and retrieve data based on combinations of those columns.
- Useful for queries with WHERE clauses involving multiple columns.
- The order of columns in the index matters for query optimization.
- Can cover multiple columns in a single index lookup.
Memory trick: A composite index is like a multi-tabbed file folder.