Why your database ignores your index, and how to make it use it

How an index actually finds rows

Every reason in this guide follows from one fact. An ordinary index — a B-tree, the default in PostgreSQL, MySQL and SQL Server — is a sorted copy of one or more columns, with a pointer from each entry back to its row. The database can find a value in it the way you find a word in a dictionary: jump to the right place, then read forward.

That only works when the query asks for something the sort order can answer: a value of the column itself, a range of values, or a prefix. The moment the condition is about something the index does not store in order — the column's lower-case form, its year, its last three letters — the dictionary is no help, and the database has to read every row and test the condition one by one. A condition an index can answer is called sargable, from "search argument". Most ignored indexes are ignored because the condition stopped being sargable, often in a way that is easy to miss.

1. A function wraps the column

The most common case looks harmless:

SELECT id FROM customers WHERE LOWER(email) = 'ana.garcia@example.com';
SELECT id FROM orders    WHERE YEAR(created_at) = 2024;
SELECT id FROM invoices  WHERE CAST(number AS int) = 1042;

The index on email is sorted by email, not by LOWER(email). To evaluate the condition, the database computes the function for every row. It has no other choice.

There are three fixes, in order of preference.

Rewrite the condition so the column stands alone

Date functions almost always have a range equivalent that the index can use directly:

SELECT id FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Store the value in the form you search for

If email addresses are saved in lower case, the query can compare them directly and the function goes away. This is usually the cleanest fix, and it also stops two rows differing only by case.

Index the expression

When neither is possible, index exactly the expression the query uses:

-- PostgreSQL
CREATE INDEX customers_email_lower ON customers (lower(email));

-- MySQL 8.0.13 and later (note the double parentheses)
CREATE INDEX customers_email_lower ON customers ((LOWER(email)));

-- SQL Server: index a computed column
ALTER TABLE customers ADD email_lower AS LOWER(email);
CREATE INDEX IX_customers_email_lower ON customers (email_lower);

The expression in the query has to match the indexed expression, so pick one spelling and keep to it.

2. The types do not match

When the two sides of a comparison have different types, the database converts one of them. If it converts the constant, nothing is lost. If it converts the column, that is a function around the column again, only invisible:

-- phone is VARCHAR
SELECT id FROM customers WHERE phone = 5551234;

MySQL compares a string column with a number by converting every row's value to a number, so the index on phone is not used. PostgreSQL refuses outright ("operator does not exist: character varying = integer"), which is at least honest. The fix in every case is to compare like with like: WHERE phone = '5551234'.

SQL Server has a subtler version of the same problem. An NVARCHAR value compared with a VARCHAR column converts the column, because NVARCHAR has higher precedence. Depending on the column's collation, SQL Server then either scans or falls back to a less efficient range seek, and it records a PlanAffectingConvert warning in the plan either way. The usual culprit is not a literal but an application driver that sends every string parameter as NVARCHAR. Set the parameter type explicitly, or make the column NVARCHAR if it really holds Unicode text.

Joins suffer the same way. Joining an INT column to a VARCHAR one, or in MySQL two columns with different character sets or collations, converts one side on every row and takes its index out of play.

3. The pattern starts with a wildcard

SELECT id FROM orders WHERE reference LIKE '%-2024';

A B-tree is sorted from the first character. LIKE 'ABC%' is a range — everything from "ABC" up to "ABD" — and can use the index. LIKE '%-2024' gives no starting point, so every row is read.

One PostgreSQL detail catches people out: with a database collation other than C, even a prefix LIKE cannot use an ordinary index. Create it with text_pattern_ops (CREATE INDEX ON orders (reference text_pattern_ops)) when prefix searches matter.

For real substring search, the options depend on the database:

• PostgreSQL: a trigram index from the pg_trgm extension serves LIKE '%...%' directly (CREATE INDEX ON orders USING gin (reference gin_trgm_ops)).
• MySQL and SQL Server: full-text indexes search for words, not arbitrary substrings. If the search is always on the end of a value, store and index the reversed value and search it with a prefix.
• Everywhere: ask whether a leading wildcard is really needed. A search box that matches from the start of a word is often what users expected anyway.

4. The index starts with a different column

An index on several columns is sorted by the first, then the second within each first value, and so on:

CREATE INDEX orders_status_created ON orders (status, created_at);

SELECT id FROM orders WHERE status = 'open' AND created_at >= '2024-01-01';  -- uses it
SELECT id FROM orders WHERE status = 'open';                                  -- uses it
SELECT id FROM orders WHERE created_at >= '2024-01-01';                      -- usually cannot

Filtering on created_at alone is like searching a phone book, sorted by surname then first name, for everyone called Maria. The first names are in order only within each surname.

Some engines can partly work around this with a skip scan that jumps through each distinct leading value: MySQL has one since 8.0.13 and PostgreSQL since version 18, and Oracle has had one for a long time. It only pays off when the leading column has few distinct values, so do not design around it.

The rule for ordering columns in a composite index: columns compared with = first, then the one column used for a range or for ORDER BY. An index on (status, created_at) serves "open orders since January, newest first" completely; one on (created_at, status) does not.

5. OR across columns, and negative conditions

SELECT id FROM orders WHERE customer_id = 42 OR reference = 'A-100';

Each side of the OR could use its own index, but the result is the union of two lookups. Engines can combine them — PostgreSQL with a BitmapOr, MySQL with an index merge — but they often estimate that a single scan is cheaper. When the two lookups are selective, say so explicitly:

SELECT id FROM orders WHERE customer_id = 42
UNION
SELECT id FROM orders WHERE reference = 'A-100';

Use UNION rather than UNION ALL here, or a row matching both conditions appears twice.

Negative conditions — <>, NOT IN, NOT LIKE — describe almost the whole table, so an index rarely helps with them even when it could technically be used. They are fine as extra conditions next to a selective positive one.

6. The planner decided the index is not worth it

Sometimes nothing is wrong with the query, and the database has simply judged that reading the table is cheaper. Following an index means one random read per matching row; scanning reads pages in order, which is much faster per row. Once a condition matches more than a small share of the table — often only a few percent — the scan wins. The same is true of any table small enough to fit in a handful of pages.

That judgement is only as good as the planner's estimates, and those can be wrong:

• Stale statistics. After a large load or delete, the planner may still believe the table is small or the value is rare. Refresh them with ANALYZE (PostgreSQL), ANALYZE TABLE (MySQL) or UPDATE STATISTICS (SQL Server).
• Skewed values. status = 'archived' may match 95% of rows and status = 'failed' 0.01%. With a parameter instead of a literal, the planner may pick one plan for both — SQL Server's parameter sniffing and PostgreSQL's generic plans are the two names for it.
• Correlated columns. The planner assumes conditions are independent. city = 'Pune' AND state = 'Maharashtra' is estimated as far rarer than it is. PostgreSQL's CREATE STATISTICS can teach it otherwise.

A row estimate far from the actual count is visible in the execution plan, which is the subject of the guide to reading EXPLAIN ANALYZE.

7. Collations, partial indexes and other near misses

• Partial and filtered indexes are used only when the query's own condition proves it is inside the index. An index WHERE status = 'open' cannot serve WHERE status = $1, because the planner cannot know $1 will be 'open'.
• Collations. A case-insensitive comparison against a case-sensitive index, or the reverse, cannot use it. In MySQL this most often appears as a join between a utf8mb3 and a utf8mb4 column.
• NULL. PostgreSQL, MySQL and SQL Server index NULL values, so IS NULL can use an index. Oracle does not store entries whose key columns are all NULL, which surprises people moving between them.
• Indexes that are not B-trees. A hash index answers only =; a full-text index only full-text predicates. Neither helps a range or an ORDER BY.

How to find out which reason it is

Guessing wastes time; the database will tell you. Run the query with EXPLAIN to see the plan it chose, and with EXPLAIN ANALYZE (or SQL Server's actual execution plan) to see what that plan really cost.

To separate "cannot use the index" from "chose not to", discourage the scan and look again. If the plan still scans, the index is unusable for this condition (reasons 1 to 5 and 7); if it switches to the index, the planner's cost judgement is the question (reason 6):

-- PostgreSQL, for this session only
SET enable_seqscan = off;
EXPLAIN SELECT ...;

-- MySQL
EXPLAIN SELECT ... FROM orders FORCE INDEX (orders_status_created) WHERE ...;

-- SQL Server
SELECT ... FROM orders WITH (INDEX (orders_status_created)) WHERE ...;

Use hints to diagnose, not as the fix: a hint freezes today's decision into code that will outlive the data it was right for.

The SQL Query Analyzer automates most of this. Its Basic level flags functions around columns (SQA004), leading wildcards (SQA003) and OR across columns (SQA005) from the query alone. Paste your CREATE TABLE and CREATE INDEX statements for the Intermediate level, and it adds type conversions (SQA106), indexes that start with the wrong column (SQA104) and filters no index can serve (SQA103). The Advanced level reads the execution plan and shows which of these the database actually ran into.

A checklist

1. Is the column alone on its side of the comparison, with no function, cast or arithmetic around it?
2. Does the value have the column's type, including VARCHAR versus NVARCHAR?
3. Does a LIKE pattern start with a fixed prefix?
4. Does the index start with the column you filter on, with equality columns before the range column?
5. Is an OR joining conditions on different columns?
6. What share of the table matches, and do the statistics know it?
7. Does a partial index's condition, the collation or the index type rule it out?

Work through them in that order. The first four account for nearly every index that "should" have been used, and each has a fix that makes the query faster without adding another index.