In this lesson, you will learn how the query optimizer uses distribution statistics—rather than guesswork—to decide whether an index is actually worth using for a specific query.
The Optimizer’s Decision-Making Process
When you run a query, the database doesn't just "see" an index and jump to it. Instead, the Query Optimizer acts like a financial analyst. It calculates the cost (in terms of I/O and CPU) of using a specific index versus performing a Full Table Scan.
To make these calculations, the database maintains Distribution Statistics. These are metadata objects—often stored as histograms—that track the data distribution within a column. For example, the optimizer looks at:
- Cardinality: How many unique values exist?
- Density: How frequently does a specific value appear?
- Range: What are the minimum and maximum values?
The "Tipping Point" Illustration
Imagine you have a Customers table with 1,000,000 rows and an index on Status. You run this query:
SELECT * FROM Customers WHERE Status = 'Active';
If the statistics tell the optimizer that 900,000 customers are marked as 'Active', the optimizer knows that using a non-clustered index would be disastrous. It would require 900,000 RID lookups (random I/O) to fetch the remaining columns, which is significantly more expensive than just reading the table sequentially from start to finish.
However, if you change the query to:
SELECT * FROM Customers WHERE Status = 'Pending_Refund';
And the statistics show only 50 rows are 'Pending_Refund', the optimizer will happily choose the index. The cost of a few index lookups is far lower than reading 1,000,000 rows.
Why Statistics Matter
If your statistics become "stale" (outdated because data changed significantly), the optimizer might work with an incorrect mental model of your data. It might choose a Full Table Scan when an index would have been faster, or worse, choose an index scan that results in a massive performance penalty. This is why the engine constantly monitors data volatility to decide when to refresh these internal histograms.
Key Takeaway: The query optimizer uses column histograms to estimate the selectivity of your filters, selecting an index only when the cost of retrieving those specific rows is lower than reading the entire table.