In this lesson, you will learn why heavily skewed data can cause the database query optimizer to abandon an efficient index in favor of a slow full table scan.
Understanding Data Skew
Data skew occurs when the distribution of values in a column is uneven. Imagine a Users table with a Status column. If 99% of your users are marked as Active and only 1% are Pending, the distribution is highly skewed.
When you query this table, the database uses histograms (which we covered in Lesson 25) to estimate how many rows will be returned. The database wants to use an index only when it’s "cheap" to do so. If the optimizer knows that 99% of your data matches a filter, it correctly realizes that performing an index lookup for every single row is far more expensive than just reading the entire table from the disk in one sequential sweep.
Why Index Efficacy Drops
When you search for a value that represents the vast majority of your data (e.g., WHERE Status = 'Active'), the index becomes a burden rather than a helper. Consider this scenario:
-- Assume 99% of rows have status = 'Active'
SELECT * FROM Users WHERE Status = 'Active';
Because the index points to specific locations scattered across the disk, the database must jump back and forth to fetch the actual rows. If it has to fetch 99% of the table, those individual "jumps" (RID lookups or key lookups) are much slower than a simple, sequential FULL TABLE SCAN.
Even though you have an index on Status, the optimizer may "suppress" it because the cost of following those pointers for nearly every row exceeds the cost of just scanning the table from start to finish. Conversely, if you search for WHERE Status = 'Pending', the index is highly effective because it quickly isolates that small 1% slice of the data.
Skewed data forces the optimizer to constantly guess whether an index is helpful or harmful, making performance unpredictable based on the specific parameter you provide.
Key Takeaway: When a query filter targets a high-frequency, skewed value, the database may bypass your index entirely, preferring a full table scan over the high cost of excessive random disk I/O.