Why Your SQL Indexes Are Not Being Used

You did everything right. You looked at your slow query, found the column it filters on, and created an index for it. You ran the query again expecting a speed boost — and nothing changed. The query plan shows a full table scan, as if the index doesn’t even exist.

This is one of the most common (and most frustrating) experiences in database work. An unused index isn’t a bug — it’s the query optimizer making a rational decision based on your query, your data, and your table structure. The fix is understanding why it made that decision.

Here are the most common reasons your indexes get ignored, and what to do about each one.

1. You’re Applying a Function to the Indexed Column

This is the single most common cause of unused indexes.

-- Index on `created_at` — but this query won't use it
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

The database can’t look up YEAR(created_at) in an index built on raw created_at values. It has to compute YEAR() for every row first — which means scanning the whole table.

SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

The same problem shows up with LOWER(email) = 'x', column + 1 = 5, or CONCAT(first, last) = 'x' — any transformation on the column side of the comparison breaks the index. If you truly need to query on a transformed value, consider a computed/generated column with its own index, or a functional index (supported by PostgreSQL, and by MySQL 8.0.13+).

2. A Leading Wildcard in LIKE

-- Index on `name` — not used
SELECT * FROM customers WHERE name LIKE '%smith';

A standard B-tree index is sorted, so it can jump straight to matches for a prefix search ('smith%'), but it can't do that for a suffix search — there's no way to binary-search for "ends with."

3. Implicit Type Conversion

-- phone_number is stored as VARCHAR, but you compare it to a number
SELECT * FROM users WHERE phone_number = 5551234567;

Many databases will silently cast one side of the comparison to match the other. If the column is a string and you compare it to a number, the database may convert the column's values instead of your literal — which disables the index.

4. Low Selectivity — The Optimizer Thinks a Scan Is Faster

Indexes help most when they narrow the result set to a small fraction of the table. If a WHERE status = 'active' filter matches 80% of rows, using the index means jumping between the index and the table (a "bookmark lookup") for millions of rows — often slower than just reading the table sequentially.

5. Outdated or Missing Statistics

The query optimizer decides whether to use an index based on estimated row counts and value distributions — not the actual data. If statistics are stale (common after large bulk inserts, deletes, or migrations), the optimizer might badly misjudge selectivity and skip a perfectly good index.

6. The Table Is Just Too Small

If a table has a few hundred rows, it likely fits in a handful of memory pages. Reading the whole table might be faster than the overhead of an index lookup. The optimizer isn't being lazy here — it's correct.

7. OR Conditions Across Different Columns

-- Separate indexes on email and phone — neither gets fully used
SELECT * FROM users WHERE email = 'a@b.com' OR phone = '5551234567';

Many optimizers struggle to use two separate single-column indexes efficiently in one OR clause, and fall back to a scan.

SELECT * FROM users WHERE email = 'a@b.com'
UNION
SELECT * FROM users WHERE phone = '5551234567';

Each half of the union can use its own index independently.

8. Wrong Column Order in a Composite Index

A composite index on (last_name, first_name) supports queries filtering on last_name alone, or on last_name AND first_name — but not on first_name alone. The index is only useful from its leftmost column inward (this is called the "leftmost prefix rule").

-- Index is (last_name, first_name) — this query can't use it
SELECT * FROM employees WHERE first_name = 'Alex';

9. NOT IN, <>, and IS NOT NULL

Negation predicates are inherently hard to satisfy efficiently with a sorted index, since "not equal to X" usually means "most of the table." The optimizer often (correctly) decides a scan is cheaper.

10. The Index Isn't "Covering," and the Table Lookup Costs More Than It Saves

An index that includes the filter column but not the columns you SELECT still requires a second trip to the actual table row for every match (a lookup). If you're selecting many columns from many matching rows, that per-row lookup cost can outweigh the benefit of the index scan itself, and the optimizer may skip it.

11. Parameter Sniffing (SQL Server Especially)

A stored procedure's execution plan gets cached based on the first set of parameter values it was called with. If that first call had unusual selectivity, the cached plan (index-based or not) may be badly suited to typical calls afterward.

12. Too Many Indexes Confusing the Optimizer (Rare, But Real)

It sounds counterintuitive, but on some engines, having many overlapping indexes on a table can lead the optimizer to make a suboptimal choice, or spend excessive time on plan selection. This is uncommon but worth knowing about if you've been indexing aggressively.

How to Actually Diagnose This

Don't guess — look at the execution plan:

• PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) SELECT ...
• MySQL: EXPLAIN SELECT ... or EXPLAIN ANALYZE SELECT ... (8.0.18+)
• SQL Server: SET SHOWPLAN_XML ON or the graphical plan in SSMS
• Oracle: EXPLAIN PLAN FOR ... then query plan_table

Look specifically for a Seq Scan / Table Scan where you expected an Index Scan / Index Seek, and check the estimated vs. actual row counts — a large mismatch is usually a statistics problem.