Why Isn't My SQL Index Being Used?

You added an index, the query still runs a full table scan, and EXPLAIN confirms the index is being ignored. Here are the seven most common reasons a database skips a perfectly good index — and how to fix each one.

Start here: the optimizer might be right

Before assuming something is broken, check whether the optimizer's choice to skip your index is actually correct. Databases choose a full table scan over an index when they estimate it's cheaper — usually because the query would return a large fraction of the table anyway, in which case jumping in and out of an index repeatedly costs more than just reading the table sequentially. If that's not the situation, one of the seven causes below is almost always the reason.

1. A function wraps the indexed column

A plain index on last_name can't be used to satisfy this WHERE clause, because the index stores raw last_name values, not UPPER(last_name) values.

-- Index on last_name is NOT used
SELECT * FROM employees WHERE UPPER(last_name) = 'SMITH';

-- Fix: a function-based (expression) index
CREATE INDEX idx_emp_upper_lastname ON employees (UPPER(last_name));
-- Or, avoid the function on the column entirely if possible:
SELECT * FROM employees WHERE last_name = 'Smith';

2. Implicit data type conversion

Comparing a NUMBER column to a string literal (or vice versa) can force a conversion that silently defeats the index.

-- If employee_id is NUMBER, comparing it to a string can block index use
SELECT * FROM employees WHERE employee_id = '100';   -- risky

-- Fix: match the literal's type to the column's type
SELECT * FROM employees WHERE employee_id = 100;

3. A leading wildcard in LIKE

A standard B-tree index is sorted left-to-right, so it can only jump straight to a matching prefix — not a matching suffix.

-- Can't use a standard index — the engine can't know where 'son' endings start
SELECT * FROM employees WHERE last_name LIKE '%son';

-- CAN use a standard index — 'K' onward is a known starting point
SELECT * FROM employees WHERE last_name LIKE 'K%';
-- Leading-wildcard searches need a different structure entirely,
-- such as a full-text index.

4. Composite index, wrong leading column

A composite (multi-column) index can only be used efficiently starting from its leftmost column — this is the "leftmost prefix rule". An index on (department_id, hire_date) can serve a query filtering on department_id alone, or on both columns together, but a query filtering on hire_date alone generally can't use that index efficiently, because the index isn't sorted by hire_date independently of department_id.

5. Stale or missing statistics

The query optimizer decides whether to use an index based on estimated row counts and selectivity — numbers it gets from statistics gathered about the table and its indexes. If those statistics are outdated (say, after a large bulk load) or were never gathered, the optimizer can badly misjudge the cost of each plan and skip a genuinely useful index. In Oracle, refresh statistics with DBMS_STATS.GATHER_TABLE_STATS; in PostgreSQL, run ANALYZE tablename.

6. Low selectivity — the index genuinely wouldn't help

If a column has only a handful of distinct values relative to the table's size (a boolean "is_active" flag on a 10-row-per-value table, for example), an index on it typically isn't selective enough to be worth using — reading the table directly is often cheaper than the overhead of an index lookup plus a row-by-row fetch. This isn't a bug; it's the optimizer correctly recognising the index wouldn't narrow things down much.

7. NULL comparisons on a plain B-tree index (Oracle-specific)

In Oracle specifically, a standard B-tree index does not store an entry for a row where every indexed column is NULL. That means WHERE commission_pct IS NULL cannot use a plain single-column index on commission_pct — there's nothing in the index to find. PostgreSQL's B-tree indexes, by contrast, do index NULLs by default, so this specific gotcha is Oracle-specific.

How to check what actually happened

Always confirm with an execution plan rather than guessing.

-- Oracle
EXPLAIN PLAN FOR SELECT * FROM employees WHERE last_name = 'King';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM employees WHERE last_name = 'King';

Frequently asked questions

Why does my index work in one query but not a very similar one?

Small differences matter enormously here — wrapping the column in a function, comparing it to a mismatched data type, or adding a leading wildcard to a LIKE pattern can each independently disable index use even if the rest of the query looks nearly identical to one that worked.

Does adding more indexes always help performance?

No. Every index adds storage and slows down INSERT/UPDATE/DELETE operations, since the index has to be maintained alongside the table. Add indexes to match actual query patterns, not preemptively — an unused index is pure overhead.

How do I force the optimizer to use a specific index?

Most databases support optimizer hints (Oracle's /*+ INDEX(...) */, for example) to force a particular plan, but treat this as a last resort — it's usually better to fix the underlying cause (function on the column, stale statistics, wrong data type) than to override the optimizer's judgment, which can go stale itself as data changes.

Why is a full table scan sometimes faster than using an index?

For small tables, or queries that will return a large percentage of a table's rows, sequentially reading the whole table can be cheaper than the overhead of repeatedly jumping between an index structure and the table's actual rows. This is a legitimate, often correct, optimizer decision — not a sign anything is broken.