Predicates an index can use in SQL

Wrap a column in a function and the index stops being an index.

CREATE INDEX orders_by_date
    ON orders (placed_at);

-- the function hides the column, so the index cannot help
SELECT COUNT(*)
FROM orders
WHERE STRFTIME('%Y', placed_at) = '2026';

SELECT COUNT(*)
FROM orders
WHERE placed_at >= '2026-01-01'
    AND placed_at < '2027-01-01';

-- LIKE ignores case, so only a NOCASE index matches it
CREATE INDEX products_sku_nocase
    ON products (sku COLLATE NOCASE);

SELECT id
FROM products
WHERE sku LIKE 'TS-%';

How it works

1. A bare column on one side lets the engine seek; a function does not.
2. Rewrite a date function as a half-open range on the raw column.
3. LIKE 'abc%' needs a NOCASE index to seek; LIKE '%abc' never can.

Keywords and builtins used here

• AND
• COLLATE
• COUNT
• CREATE
• FROM
• INDEX
• LIKE
• ON
• SELECT
• WHERE

The run, in numbers

Lines

20

Characters to type

443

Tokens

65

Three-star pace

95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 41 seconds.

Type this snippet

Step 1 of 3 in Writing it faster, step 8 of 17 in Plans & performance.

← Previous Next →