CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is investigating a report of slow data retrieval for a specific table (`orders`) that has millions of rows. Queries frequently filter by `customer_id` and then sort by `order_date`. The current index is only on `customer_id`. To significantly improve the performance of these queries, which of the following index types would be MOST effective?
- AA full-text index on `order_details`.
- BA composite index on `(customer_id, order_date)`.
- CA unique index on `order_id`.
- DA non-clustered index on `order_date`.
Show answer & explanationAnswer & explanation
Correct answer: B. A composite index on `(customer_id, order_date)`.
A composite index on `(customer_id, order_date)` allows the database to efficiently filter by `customer_id` and then use the pre-sorted `order_date` within each customer's data directly from the index, eliminating the need for a separate sort operation.
Why the other options are wrong
- A. A full-text index is used for keyword searches within text columns and is irrelevant for filtering by IDs and dates.
- C. A unique index on `order_id` is for primary key enforcement and won't help filtering by `customer_id` or sorting by `order_date`.
- D. An index on `order_date` alone would help sorting but not filtering by `customer_id` efficiently first.
Composite Index
An index created on two or more columns in a table. It is effective for queries that filter or sort on a combination of these columns, especially when the leading column (first in the index definition) is used for filtering.
- Optimizes queries using multiple columns in WHERE or ORDER BY clauses.
- Order of columns in the index is crucial for performance.
- Can satisfy both filtering and sorting requirements directly from the index.
Memory trick: Two keys are better than one for finding the right door and organizing its contents.