Slow queries
In short: A query stays slow despite an index when the column is inside a function or LIKE, when it selects every column, when an OR also tests a column without an index, or when it counts everything to answer "is there any".
Problem
The index is there, and it is still slow
The yearly report counts the visits in 2026. There is an index on seen_at. Run the query and look at the time:
And the plan:
SCAN visits USING COVERING INDEX. The database reads the index because it is smaller than the table, but it reads all of it: all 300,000 entries. There is no search.
The idea
The column has to stand alone
The index is sorted by seen_at. It is not sorted by substr(seen_at, 1, 4). To check the condition, the database has to compute the function for every entry, and for that it has to go through all of them.
The same condition can be written with bounds, where the column stands alone on one side:
The result is the same, and the plan is SEARCH. Compare the times of the two queries.
The same goes for arithmetic. customer_id + 0 = 1234 gives the same as customer_id = 1234, but the index does not help:
The rule: do not touch the column in WHERE. Arithmetic moves to the other side (price > 10 / 1.2, not price * 1.2 > 10), and a function on a date becomes a range.
Try it
October
The field holds the query from the yearly report. The monthly report wants the visits in October 2026. Write the condition so that it uses the index on seen_at.
Solution
SELECT COUNT(*) FROM visits
WHERE seen_at >= '2026-10-01' AND seen_at < '2026-11-01';WHERE seen_at >= '2026-10-01' AND seen_at < '2026-11-01'. 9,988 visits. The check also looks at the plan: with substr(seen_at, 1, 7) = '2026-10' or LIKE '2026-10%' the result is right, but the plan is SCAN and the answer does not pass. With the bounds the plan is SEARCH … (seen_at>? AND seen_at<?). The upper bound is “before 1 November”, not “up to 31 October”: that way you do not think about how many days the month has, and a value with a time (2026-10-31 18:30) is included too.
See it
LIKE and the index
It looks as if LIKE '2026-10%' should use the index: the start is known, as in a phone book. Run the plan:
In SQLite it is SCAN. The reason: LIKE in SQLite ignores the difference between upper and lower case, while the index is sorted with that difference. SQLite uses an index for LIKE 'start%' only under certain conditions, for example when the column or the index is COLLATE NOCASE. For dates the reliable way is the range from the previous card.
A pattern that starts with % cannot use an ordinary index in any database. The start is unknown, so the search has nowhere to begin:
More precisely, if you are curious
The exception to the rule about functions is an index on an expression: CREATE INDEX idx_visits_year ON visits(substr(seen_at, 1, 4)) makes WHERE substr(seen_at, 1, 4) = '2026' a SEARCH again. The index only helps queries with the same expression.
In SQLite, GLOB '2026-10*' is case-sensitive and does use the index (the plan is SEARCH). So does PRAGMA case_sensitive_like = ON, but it changes the behaviour of every LIKE on the connection.
In PostgreSQL an ordinary index helps with LIKE 'start%' only with the C collation or when it is created with text_pattern_ops. For searching in the middle of a text there are separate kinds of indexes (the pg_trgm extension). Full-text search is in the module on kinds of databases.
The idea
Only the columns you need
The program shows on which dates customers 1000 to 1100 visited. Two queries with the same rows. Compare the times:
The second one is faster, because customer_id and seen_at are in the index (customer_id, seen_at), and it is covering: the database does not open the table. SELECT * also wants product_id, so for each of the 6,060 entries the database goes to the table too. Put in SELECT only the columns the program actually uses. That way less data travels to the program as well.
The idea
OR and IN
OR on the same column is the same as IN. SQLite runs them the same way:
The trap is OR across different columns. There is no index on product_id. Rows with product_id = 4 can be anywhere in the table, so the database reads everything, and the index for the customer does not help:
If both columns have an index, SQLite searches each one separately and combines the results (the plan says MULTI-INDEX OR). Without an index for one half it stays SCAN.
The idea
“Is there any”, not “how many”
The page of product 13 shows “Popular” if anyone has viewed it. Counting finds all 12,000 visits:
The page needs only “yes” or “no”. EXISTS stops at the first row it finds:
There is no index on product_id, but EXISTS is still fast, because the first visit of the product is near the start of the table. If there is no such row, EXISTS will also read everything. So here too an index helps. Another way is SELECT 1 FROM … WHERE … LIMIT 1: an empty result means “no”.
Exercise
Two tasks
1. The field holds the old query for 2026. Customer 1234 asks how many times they looked at products in 2025. Write it so that the plan is SEARCH on both the customer and the date.
Customer 1234’s visits in 2025, with SEARCH on both columns:
Solution
SELECT COUNT(*) FROM visits
WHERE customer_id = 1234
AND seen_at >= '2025-01-01' AND seen_at < '2026-01-01';WHERE customer_id = 1234 AND seen_at >= '2025-01-01' AND seen_at < '2026-01-01'. 33 visits. The check does not let a SCAN through. Whether the search uses both columns, see with EXPLAIN QUERY PLAN. The two fixes are the column without + 0 and the range instead of substr. With EXPLAIN QUERY PLAN in front of the query it says SEARCH visits USING COVERING INDEX idx_visits_customer_seen (customer_id=? AND seen_at>? AND seen_at<?).
2. The “View again” button is shown if customer 4321 has viewed a product from 1 October 2026 onwards. Return 1 or 0 in a column seen.
Does customer 4321 have visits from 2026-10-01 onwards?
Solution
SELECT EXISTS (
SELECT 1 FROM visits
WHERE customer_id = 4321 AND seen_at >= '2026-10-01'
) AS seen;SELECT EXISTS (SELECT 1 FROM visits WHERE customer_id = 4321 AND seen_at >= '2026-10-01') AS seen; Yes: 1. The check compares only the value, so COUNT(*) > 0 will pass too, but it counts all the visits when only the first one is needed.
Test