Adding an index to a database table is one of the first things developers consider when a query becomes slow. An index can make a large difference, but creating one does not guarantee that every query against the table will use it.
This can be confusing when a table has an index on exactly the column being filtered.
For example, you may have:
CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId);
and still see a query like this perform a table scan:
SELECT *
FROM Orders
WHERE CustomerId = 1001;
The index exists, the query looks correct, and yet the database chooses to read a large part of the table.
This is usually not a database failure. The query optimizer is making a cost-based decision based on the available statistics, estimated number of matching rows, query shape, and other factors.
Understanding why that decision happens is more useful than simply adding another index.
How a Database Decides Whether to Use an Index
When a query is executed, the database optimizer looks at different possible execution strategies.
For a query such as:
SELECT *
FROM Orders
WHERE CustomerId = 1001;
the optimizer may consider:
Index Seek
|
v
Find matching CustomerId values
|
v
Read required rows
or:
Table Scan
|
v
Read table pages
|
v
Check CustomerId for each row
The optimizer estimates the cost of both approaches.
If the table is small, or if a large percentage of rows match the condition, reading the table sequentially can be cheaper than using the index and then fetching many rows from the table.
This is why an existing index does not automatically mean an index seek is the correct plan.
Index Seek and Table Scan Are Different Operations
An index seek means the database can navigate the index directly to the relevant part of the data.
For example:
CustomerId = 1001
|
v
Index
|
+---- matching entries
|
v
Data rows
A table scan is different.
The database reads pages from the table and evaluates the predicate against the rows it encounters.
Conceptually:
Table
|
+-- Page 1 -> check rows
+-- Page 2 -> check rows
+-- Page 3 -> check rows
+-- Page 4 -> check rows
...
Neither operation is automatically good or bad.
The correct choice depends on the amount of data that needs to be read.
For example, if a query needs almost every row in a table, an index may not provide much benefit.
The First Thing to Check: The Execution Plan
When a query ignores an index, do not immediately create another index.
Start with the execution plan.
For example, in SQL Server:
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT *
FROM Orders
WHERE CustomerId = 1001;
The actual execution plan can show whether SQL Server selected an Index Seek, Index Scan, Table Scan, Key Lookup, or another operator.
The important question is not simply:
"Did the database use my index?"
Instead ask:
"Why did the optimizer choose this execution plan?"
That question leads to the actual problem.
The Query May Return Too Many Rows
One common reason for a table scan is low selectivity.
Suppose an Orders table contains 10 million rows, and the index is:
CREATE INDEX IX_Orders_Status
ON Orders(Status);
Now consider:
SELECT *
FROM Orders
WHERE Status = 'Completed';
If most orders have the Completed status, the index may not be useful enough.
Using the index could involve:
Finding many matching index entries.
Following those entries to the table.
Reading a large number of data pages.
Returning the rows.
A sequential scan may be cheaper.
The important concept is selectivity.
A highly selective condition returns relatively few rows.
For example:
WHERE OrderId = 987654321
is usually much more selective than:
WHERE Status = 'Completed'
when most orders are completed.
Selectivity Matters More Than the Existence of an Index
Consider a table with one million rows.
If a query returns five rows, an index can be very useful.
If the query returns 900,000 rows, the database has much more data to process.
The index itself does not change this fact.
A useful mental model is:
Few matching rows
|
v
Index often helps
Many matching rows
|
v
Scan may be cheaper
This is why simply checking whether an index exists is not enough.
You need to understand the distribution of values in the column.
Statistics Can Cause the Optimizer to Make the Wrong Choice
Query optimizers rely heavily on statistics.
Statistics provide information about the data distribution, such as how common particular values are and how many rows the optimizer expects to match a predicate.
Suppose the optimizer estimates:
Expected rows: 100
but the query actually returns:
Actual rows: 500,000
The execution plan chosen using the original estimate may be inefficient.
This can happen when statistics are outdated or when the data distribution has changed significantly.
In SQL Server, you can inspect statistics and update them when appropriate.
For example:
UPDATE STATISTICS Orders;
This should not be treated as a universal solution. Updating statistics unnecessarily can create additional work, and the correct maintenance strategy depends on the database and workload.
The important point is that an index and its statistics are closely related to the optimizer's decision.
Data Growth Can Change an Execution Plan
A query that was fast yesterday may become slower after significant data growth.
Imagine an application initially has:
100,000 rows
and later grows to:
50,000,000 rows
The same index may still exist.
The same SQL query may still exist.
But the cost calculation can change dramatically.
This is particularly common in systems where a table grows continuously, such as:
transaction tables
audit logs
event data
telemetry
order history
application logs
Performance testing should therefore consider realistic data volumes rather than relying only on development databases.
The Query May Prevent Efficient Index Usage
Sometimes the problem is not the index. The query itself makes efficient index access difficult.
For example:
SELECT *
FROM Users
WHERE LOWER(Email) = '[email protected]';
The database may need to evaluate LOWER against many rows depending on the database engine and indexing strategy.
A function applied to an indexed column can make normal index access less effective.
The exact behavior depends on the database system and the available indexes.
If the application frequently searches using a transformed value, consider a schema or indexing strategy designed for that access pattern rather than repeatedly applying the transformation at query time.
Data Type Mismatches Can Also Cause Problems
Another issue is comparing different data types.
For example, suppose UserId is stored as an integer:
UserId INT
but the application sends a string parameter:
"12345"
The database may need to perform an implicit conversion.
Depending on the database engine and which side of the comparison is converted, this can affect index usage.
The safer approach is to use the correct parameter type from the application.
For example, in C#:
var userId = 12345;
command.Parameters.Add("@UserId", SqlDbType.Int).Value = userId;
The exact API depends on the data-access library, but the principle remains the same: keep parameter types consistent with the database schema.
A Covering Index Can Change the Cost
Sometimes the database does use an index, but the query is still expensive because it needs additional columns from the base table.
For example:
SELECT CustomerId, OrderDate, TotalAmount
FROM Orders
WHERE CustomerId = 1001;
An index on CustomerId may locate the matching rows, but the database may then need additional lookups to retrieve OrderDate and TotalAmount.
If this happens frequently, a covering index may be appropriate.
For example:
CREATE INDEX IX_Orders_CustomerId
ON Orders(CustomerId)
INCLUDE (OrderDate, TotalAmount);
Now the index contains the columns required by the query.
The database may be able to answer the query directly from the index without repeatedly accessing the base table.
However, adding included columns increases index size and maintenance cost.
Do not turn every frequently queried table into a collection of large covering indexes.
Key Lookups Can Make an Index Look Worse Than a Scan
A common execution plan pattern is:
Index Seek
|
v
Key Lookup
|
v
Return rows
This can be efficient when only a few rows are returned.
But if the index matches thousands or millions of rows, repeated lookups can become expensive.
At some point, the optimizer may decide that scanning the table is cheaper.
For example:
Index Seek + 500,000 lookups
|
v
High I/O cost
versus:
Table Scan
|
v
Sequential page reads
This is one reason an index can exist and still not appear in the final execution plan.
Parameter Sniffing Can Change the Plan
In SQL Server, parameterized queries can introduce another issue called parameter sniffing.
Suppose a query uses:
SELECT *
FROM Orders
WHERE CustomerId = @CustomerId;
One customer might have five orders.
Another customer might have five million orders.
A plan that is efficient for the first customer may not be efficient for the second.
The same query can therefore behave differently depending on the parameter value and the execution plan that was compiled and reused.
If you suspect parameter sniffing, inspect the execution plan and compare estimated versus actual row counts for different parameter values.
Do not immediately add indexes. The problem may be plan selection rather than missing indexing.
A Query Can Scan the Index Instead of the Table
There is also an important distinction between an Index Scan and a Table Scan.
An Index Scan means the database reads a large portion of an index.
That can still be a reasonable plan.
For example, suppose an index is much narrower than the base table.
Reading the entire index may require fewer pages than reading the entire table.
So this:
Index Scan
is not automatically a performance problem.
Similarly, this:
Table Scan
is not automatically evidence that the database is poorly configured.
Always look at logical reads, CPU time, execution duration, returned rows, and the actual execution plan.
A Practical Troubleshooting Process
When you discover that a query is not using the expected index, use a consistent process.
Step 1: Capture the Actual Query
Do not investigate a simplified version if the application is executing something different.
Capture the actual SQL and parameters used by the application.
Step 2: Check the Actual Execution Plan
Look for:
Step 3: Check the Number of Rows
Determine how many rows actually match the predicate.
If the query returns a large percentage of the table, a scan may be completely reasonable.
Step 4: Check Statistics
Look for stale or inaccurate statistics and verify that the optimizer has useful information about the data distribution.
Step 5: Check Predicates
Look for:
Step 6: Check Existing Index Design
Ask whether the index actually supports the query.
Look at:
key columns
column order
included columns
index width
duplicate indexes
overlapping indexes
Step 7: Measure Before Changing Anything
Capture baseline metrics before modifying the query or index.
This gives you a reliable way to determine whether the change actually improved performance.
Common Mistakes
Creating More Indexes Without Reading the Plan
More indexes can increase storage and write overhead without fixing the real problem.
Assuming Every Query Should Use an Index
The optimizer can legitimately choose a scan.
Ignoring Data Distribution
An index on a low-selectivity column may not provide much benefit.
Using SELECT *
Returning unnecessary columns can increase I/O and make covering strategies more difficult.
Ignoring Actual Row Counts
Estimated and actual row counts should be compared when investigating unexpected plans.
Testing Only With Small Development Data
A query that works well against 10,000 rows may behave very differently against millions of rows.
Best Practices
Always inspect the execution plan before changing indexes.
Compare estimated rows with actual rows.
Understand the selectivity of indexed columns.
Keep statistics current according to the database maintenance strategy.
Avoid unnecessary functions and implicit conversions in predicates.
Consider covering indexes when repeated key lookups are expensive.
Test with production-like data volumes.
Measure logical reads, CPU, and execution time before and after changes.
Remove duplicate or unnecessary indexes instead of continuously adding new ones.
Remember that a scan is not automatically a performance problem.
Conclusion
An existing index does not guarantee that a query will use an index seek. The optimizer considers selectivity, statistics, row counts, query shape, lookup costs, and the overall cost of each available execution plan.
When a query scans the table despite having an index, start with the execution plan and actual row counts. Then investigate statistics, predicates, data types, and index design before making changes.
Join the conversation! Your thoughts help the community grow.