SQLite Indexes and Why LIKE %x Will Not Use Them

Indexes in SQLite are B-trees ordered by the indexed columns. A lookup can seek to a left-most prefix and scan a contiguous range. That is why WHERE email = 'a@b.com' can be O(log n), and why WHERE email LIKE '%gmail.com' cannot. The leading percent means the match can start anywhere in the string. There is no ordered place to seek. SQLite will SCAN the table (or the whole index) and apply the LIKE as a filter.

This is the same in Postgres, MySQL, and every other B-tree. People still ship it because the table is small in development and the LIKE looks like a search box. On a VPS with a few million rows it becomes a CPU hot spot you will misread as disk.

What an index can actually do

A single-column index on email supports:

• equality: email = ?
• prefix range: email > ? AND email < ?
• prefix LIKE: email LIKE 'alice%' (the pattern has a fixed left prefix)
• ORDER BY email, sometimes as a covering scan

It does not support:

• leading wildcard: email LIKE '%alice'
• infix wildcard: email LIKE '%alice%'
• functions: WHERE lower(email) = ? unless you indexed lower(email)
• expressions on the other side that hide the column

A composite index on (account_id, created_at) supports WHERE account_id = ? and WHERE account_id = ? AND created_at > ?, and it can satisfy ORDER BY created_at for a single account. It does not help WHERE created_at > ? alone. Left-most prefix is the rule. Write the index in the same order you filter.

CREATE INDEX events_account_created
  ON events(account_id, created_at);

EXPLAIN QUERY PLAN
SELECT id FROM events
WHERE account_id = 42 AND created_at >= '2026-05-01'
ORDER BY created_at DESC
LIMIT 50;

EXPLAIN QUERY PLAN
SELECT id FROM events
WHERE kind LIKE '%signup%';

The first plan should SEARCH using events_account_created. The second should SCAN. kind LIKE '%signup%' is a substring search. An index on kind does not change that, because the pattern has no left prefix.

LIKE rules, collations, and nocase

LIKE is case-insensitive for ASCII by default (the PRAGMA case_sensitive_like default is false). That surprises people who indexed a BINARY column and expected a seek. For prefix LIKE to use an index, the index collation and the LIKE collation must agree. If you need case-insensitive prefix search, index the expression:

CREATE INDEX accounts_email_lower ON accounts(lower(email));

SELECT id FROM accounts
WHERE lower(email) LIKE 'alice%';

lower(email) LIKE 'alice%' can seek. lower(email) LIKE '%alice%' cannot. The expression index only helps when the query writes the same expression on the left and a prefix pattern on the right.

GLOB is case-sensitive and uses * as a wildcard. Same prefix rule. If you need true substring search, stop fighting LIKE and use FTS5 or a reverse column.

Three ways to search the end of a string

Suffix search (find *.gmail.com) is a prefix search on the reversed string.

• Store reverse(email) in a column or generated column, index it, query WHERE email_rev LIKE reverse('%gmail.com') which becomes a prefix LIKE on the reversed value.
• Use FTS5 when the field is prose or tokens, not a single identifier. FTS5 is an inverted index; it is the right tool for body text, not for emails.
• Pull a domain into its own column at write time. Then WHERE domain = 'gmail.com' is an equality, which is what you wanted.

Do not add ten indexes because a dashboard has ten filters. Each index slows writes and uses disk. Build the index that matches the WHERE plus ORDER BY of the hot query, confirm with EXPLAIN QUERY PLAN, then ANALYZE.

On High-Speed SSD a table scan of a few hundred thousand rows may still feel fine. Measure anyway. The plan is free; an outage when the table grows is not.

If the pattern starts with a wildcard, the B-tree cannot help; change the query shape. You can spin up an Ubuntu 24.04 instance on Netbay in under 60 seconds and follow along — netbayhosts.in.