When Indexes Don't Kick In — Patterns and Fixes

Patterns where an index exists but doesn't kick in

Even after you create an index, certain ways of writing the condition keep it from being used and the query still runs as a full table scan (SCAN).

The three classic culprits are below.

• Conditions that wrap the column in a function or arithmetic (substr(col) / upper(col) / col + 0, and so on)
• Middle-match LIKE '%x%'
• Negation conditions (<> / NOT IN)

In every case the cause is the same: the comparison no longer lines up with the order stored in the index.

Later in the article we look at expression indexes for the cases where you can't avoid wrapping the column in a function, and partial indexes for narrowing the index down to a specific subset of rows.

Throughout this article you'll compare "the way that doesn't use the index" with "the way that does" side by side in the query plan.

-- Even with an index on amount,
-- wrapping the column in a function falls back to a full table scan
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(amount);

-- Compare the column as-is → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE amount >= 800000;

-- Wrap the column in a function → back to SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE abs(amount) >= 800000;

Consider the requirement: "I want to count only sales from 2024." Create an index on sale_date, then compare two query plans side by side — one that wraps the column in a function, and one that uses a range condition with the column as-is — and watch how SCAN versus SEARCH changes. Rebuild the index inside this single console execution so the plan is fully self-contained. (Run it correctly to reveal the explanation.)

① Drop any existing index with DROP INDEX IF EXISTS and create a single-column index on sale_date.

② Use EXPLAIN QUERY PLAN on a query that pulls the year out of sale_date with substr and compares it with '2024' (the form that wraps the column in a function).

③ Then show the plan for a query that expresses the same meaning as a range condition on sale_date (greater than or equal to the start of 2024, less than the start of the next year) and compare it with ②.

Middle-match LIKE

A prefix-match LIKE (a form where the start is fixed, like 'P00%') can in principle narrow down a range using the index order.

In contrast, a middle-match ('%5%') or suffix-match ('%50') has no fixed start, so no database can walk the index and the query becomes a full table scan.

That said, in SQLite, `LIKE` is case-insensitive by default, so it doesn't line up with the usual BINARY-ordered index — even a prefix-match LIKE doesn't get a SEARCH and falls back to SCAN (see the callout below).

So when you want a prefix match in SQLite to use an index lookup, the straightforward fix is to rewrite it as a range condition, like product >= 'P00' AND product < 'P01'.

For middle-match LIKE, if you really need it as a requirement, consider a different mechanism such as full-text search.

SQLite's LIKE is case-insensitive by default

In SQLite, PRAGMA case_sensitive_like is OFF by default, so LIKE is a case-insensitive comparison.

Indexes are built using the standard BINARY ordering (case-sensitive dictionary order), so even a prefix-match like LIKE 'P00%' doesn't line up with the index order and the comparison rule — it isn't used as a SEARCH.

If you run LIKE 'P00%' in this console, the plan becomes SCAN perf_sales USING COVERING INDEX.

The straightforward workarounds are to rewrite it as a range condition (product >= 'P00' AND product < 'P01'), or to enumerate the case variants on the value side (product IN ('P050', 'p050')).

In this article we go with the most direct option — rewriting it as a range condition.

In MySQL / PostgreSQL, if LIKE is configured to be case-sensitive, a prefix match usually becomes a SEARCH.

-- Compare range condition and LIKE plans against an index on product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Range condition → fixed start, so SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE product >= 'P15' AND product < 'P16';

-- Prefix-match LIKE → SCAN in SQLite (case-insensitive)
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE 'P15%';

-- Middle-match LIKE → SCAN in any DB
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product LIKE '%9%';

Take the requirement "I want to count sales whose product code starts with a particular prefix" and compare the plans for three forms: (A) a range condition, (B) a prefix-match LIKE, and (C) a middle-match LIKE. Create an index on product and watch which form becomes a SEARCH in SQLite.

① Drop any existing index with DROP INDEX IF EXISTS and create a single-column index on product.

② Use EXPLAIN QUERY PLAN on a query that counts rows with the range condition WHERE product >= 'P00' AND product < 'P01'.

③ Show the plan for the same meaning written as a prefix-match LIKE 'P00%' and compare it with ②.

④ Then also show the plan for a middle-match LIKE '%5%'.

Wrapping the column with upper() or arithmetic stops the index from being used

When you apply a function or arithmetic to a column to filter it, the column's index can't be used even if one exists.

The reason is that the index is ordered by the raw values of the column, so a comparison against a transformed value like upper(product) or amount + 10000 no longer lines up with the index order.

For example, WHERE upper(product) = 'P050' has to push every row's product through upper() before comparing, so the index on product isn't used and the query falls back to a full scan.

Likewise, WHERE amount + 10000 = 900000 has to compute amount + 10000 for every row before comparing, so the index on amount isn't used.

The fix is to leave the column alone and adjust on the value side instead.

upper(product) = 'P050', if the only expected values are 'P050' and 'p050', becomes WHERE product IN ('P050', 'p050') — and IN lets the index narrow multiple values at once.

amount + 10000 = 900000 can be rewritten directly as amount = 800000 by subtracting 10000 from both sides, and the index on amount narrows it in one shot.

Rewriting substr(sale_date, 1, 4) as a range condition in the previous section is the same "don't transform the column" idea applied to dates.

-- Example of wrapping the column with lower() against an index on product
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(product);

-- Wrap the column with lower() → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE lower(product) = 'p001';

-- Enumerate case variants on the value side → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE product IN ('P001', 'p001');

-- Example of arithmetic wrapping (against an index on qty)
DROP INDEX IF EXISTS ix_qty;
CREATE INDEX ix_qty ON perf_sales(qty);

-- Wrap with qty * 2 → SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty * 2 = 20;

-- Simplify the expression on the value side (divide both sides by 2) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE qty = 10;

Consider the requirement: "I want to count sales for product code 'P050' (or its lowercase form 'p050')."

Create an index on product and compare the plans for two forms — wrapping the column with upper() versus enumerating the candidate values with IN (...) on the value side.

Both forms target the same comparison covering 'P050' and 'p050', so the result is identical.

① Drop any existing index with DROP INDEX IF EXISTS and create a single-column index on product.

② Use EXPLAIN QUERY PLAN on a query that wraps the column with upper() as in WHERE upper(product) = 'P050'.

③ Show the plan for the same meaning written as WHERE product IN ('P050', 'p050') and compare it with ②.

For the requirement "I want to count sales with an amount of exactly 800,000", create an index on amount and compare the plans for two forms — applying arithmetic to the column versus adjusting on the value side.

amount + 10000 = 900000 becomes amount = 800000 once you subtract 10000 from both sides — mathematically completely equivalent.

① Drop any existing index with DROP INDEX IF EXISTS and create a single-column index on amount.

② Use EXPLAIN QUERY PLAN on a query that applies arithmetic to the column as in WHERE amount + 10000 = 900000.

③ Show the plan for the same meaning written as WHERE amount = 800000 and compare it with ②.

Negation conditions (<>, NOT IN)

Negation conditions like <> (not equal) or NOT IN often end up targeting "most of the table", so there's no point in narrowing with an index.

For example, when status is 'paid' (around 40,000 out of 50,000 rows) / 'pending' (around 5,000) / 'refunded' (around 5,000), WHERE status <> 'paid' targets 10,000 rows — about 20% of the table.

The selectivity isn't low enough, so the optimizer decides a full table scan is cheaper.

The fix is to rewrite as an equality condition.

WHERE status <> 'paid' can be rewritten as WHERE status IN ('pending', 'refunded'), and that form can narrow the target rows via the index.

-- Compare NOT IN vs IN rewriting against an index on status
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(status);

-- Negation condition (NOT IN) → wide target, SCAN
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status NOT IN ('refunded');

-- Rewritten as equality (IN) → SEARCH
EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales WHERE status IN ('paid', 'pending');

Consider the requirement: "I want to count sales other than paid." Create an index on status and compare the plans between writing it with <> and rewriting the same meaning as an equality condition with IN.

① Drop any existing index with DROP INDEX IF EXISTS and create a single-column index on status.

② Use EXPLAIN QUERY PLAN on a query that counts rows matching WHERE status <> 'paid'.

③ Show the plan for the same meaning rewritten as WHERE status IN ('pending', 'refunded') and compare it with ②.

Fix it with expression indexes and partial indexes

When wrapping the column in a function is unavoidable, an expression index (an index built on the result of an expression, not the raw column) saves the day.

With CREATE INDEX ix ON perf_sales(substr(sale_date,1,4)), creating an index on the same expression that the condition uses turns the comparison on that expression into an index lookup.

The requirement is that the expression in the condition matches the expression in the index.

The other tool is a partial index (an index with a WHERE clause, built only for a specific subset of rows).

If status='pending' rows are only part of the table (5,000 out of 50,000), you can build a narrowed index like CREATE INDEX ix ON perf_sales(emp_id) WHERE status='pending'.

The index itself stays small and efficiently narrows queries that include that condition.

-- Expression index: build it on the same expression the condition uses
DROP INDEX IF EXISTS ix_demo;
CREATE INDEX ix_demo ON perf_sales(substr(sale_date, 1, 4));

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM perf_sales
WHERE substr(sale_date, 1, 4) = '2024';

Consider the requirement: "I want to look up in-progress (pending) sales narrowed down by sales rep." Pending is a small subset (5,000 out of 50,000), so build a partial index that targets only those rows and confirm that a query plan including status='pending' becomes an index lookup.

① Drop any existing index with DROP INDEX IF EXISTS.

② On emp_id, create a partial index (using the CREATE INDEX ... WHERE ... form) targeting only rows where status equals 'pending'.

③ Use EXPLAIN QUERY PLAN on a query that counts rows where status is 'pending' and a specific emp_id, and confirm the partial index is being used.