CompTIA DataSys+ (DS0-001)Database FundamentalsHard
A junior database administrator is tasked with improving the performance of a frequently executed query that joins two large tables, `Orders` and `Customers`, on the `CustomerID` column. The `CustomerID` column in both tables is already indexed. However, the query still experiences slow execution times. Upon closer inspection, it's noted that the `Orders` table has millions of rows, and the `CustomerID` column in `Orders` has many duplicate values, while in `Customers` it is unique. What is the MOST likely reason for the continued slow performance?
- AThe database is experiencing high disk I/O due to full table scans on the `Orders` table.
- BThe `CustomerID` column in the `Customers` table is not indexed.
- CThe `CustomerID` index on the `Orders` table has low selectivity due to many duplicate values.
- DThe data types of `CustomerID` in `Orders` and `Customers` tables do not match.
Show answer & explanationAnswer & explanation
Correct answer: C. The `CustomerID` index on the `Orders` table has low selectivity due to many duplicate values.
An index's effectiveness is tied to its selectivity. When a column has many duplicate values (low selectivity), the index might not significantly reduce the number of rows the database has to scan or process, leading to slower query performance despite the index's existence.
Why the other options are wrong
- A. While possible, the presence of an index should reduce full table scans. The provided information points to an issue with the index's utility rather than its absence.
- B. The problem states that both `CustomerID` columns are already indexed, making this incorrect.
- D. If data types didn't match, the join would likely fail or produce incorrect results, not just be slow. The scenario implies the join works but is slow.
Index Selectivity
A measure of how unique the values in an indexed column are. High selectivity (many unique values) makes an index very effective; low selectivity (many duplicates) reduces its effectiveness.
- High selectivity means the index quickly narrows down results.
- Low selectivity means the index may still lead to scanning many rows.
- Indexes on columns with many duplicates (low selectivity) are less beneficial for performance.
Memory trick: INDEX speed depends on SELECTIVITY, not just existence!