5 Reasons Your SQL Index Is Being Ignored (and How to Fix Them)

You added an index, but the query is still slow. The database may be ignoring the index completely and reading every row. Here are the five most common reasons, and how to fix each one.

I checked every query plan below in SQLite on a 100,000-row table. The same rules apply to PostgreSQL and MySQL.

Video version:

First: ask the database with EXPLAIN

Don't guess. Put EXPLAIN in front of the query (EXPLAIN QUERY PLAN in SQLite, EXPLAIN or EXPLAIN ANALYZE in Postgres and MySQL):

EXPLAIN QUERY PLAN SELECT * FROM users WHERE email = '[email protected]';
SEARCH users USING INDEX idx_email (email=?)   ✅ index used

EXPLAIN QUERY PLAN SELECT * FROM users WHERE DATE(created_at) = '2025-06-01';
SCAN users                                     ❌ reads every row

SEARCH … USING INDEX (or Index Scan in Postgres) means the index is working. SCAN (or Seq Scan) means a full table scan.

1. A function wrapped around the column

The most common one:

-- ❌ SCAN users
SELECT * FROM users
WHERE DATE(created_at) = '2025-06-01';

-- ✅ SEARCH USING INDEX
SELECT * FROM users
WHERE created_at >= '2025-06-01'
  AND created_at <  '2025-06-02';

The index stores created_at, not DATE(created_at), so the database can't use it to find the matches. Rewrite the filter as a range on the raw column. In my test both versions returned the same 288 rows, and the range version was over 100× faster.

Need the function? Index the expression

Sometimes you really do need it, as in a case-insensitive email lookup. Then index the expression itself:

CREATE INDEX idx_email_lower ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- SEARCH users USING INDEX idx_email_lower ✅

PostgreSQL, SQLite and MySQL 8 all support expression indexes. The query has to match the indexed expression exactly.

2. LIKE with a leading wildcard

-- ❌ SCAN users
SELECT * FROM users WHERE email LIKE '%@gmail.com';

An index is sorted from the first character, like a phone book. If you don't know how the value starts, there's nowhere to jump to.

The fix is to store the part you search on in its own indexed column:

CREATE INDEX idx_domain ON users (email_domain);

SELECT * FROM users WHERE email_domain = 'gmail.com';  -- ✅

For real text search, use full-text search (tsvector in Postgres, FULLTEXT in MySQL, FTS5 in SQLite) instead of LIKE '%…%'.

3. Math on the column

The same problem as #1, in disguise:

-- ❌ SCAN users
SELECT * FROM users WHERE balance * 1.2 > 1000;

-- ✅ SEARCH USING INDEX
SELECT * FROM users WHERE balance > 1000 / 1.2;

The index stores balance, not balance * 1.2. Move the math to the other side of the comparison so the column stands alone.

4. Composite index: skipping the leftmost column

CREATE INDEX idx_country_city ON users (country, city);

-- ❌ SCAN users: city alone can't use this index
SELECT * FROM users WHERE city = 'Austin';

A composite index on (country, city) is sorted by country first, then by city within each country. Searching by city alone is like looking up a first name in a phone book sorted by last name.

The fixes are:

-- filter on the leftmost column too
SELECT * FROM users WHERE country = 'US' AND city = 'Austin';  -- ✅

-- or give city its own index
CREATE INDEX idx_city ON users (city);

When you design a composite index, put the column you always filter on first.

5. OR with an unindexed side

-- only email is indexed
-- ❌ SCAN users
SELECT * FROM users
WHERE email = '[email protected]' OR city = 'Austin';

To find the city = 'Austin' rows, the database has to scan everything anyway, so it doesn't bother with the email index either. Index both columns, and it can search each index and combine the results (SQLite shows MULTI-INDEX OR, Postgres shows a BitmapOr):

CREATE INDEX idx_city ON users (city);
-- now both sides of the OR can use an index ✅

Cheat sheet

And always check with EXPLAIN before and after. 👀

I post one tested coding tip every day on YouTube at Refactorly. The 30-second version of this one is here.