5.Teaching the Optimizer

OrderDate has an index, but this query doesn't filter on OrderDate directly — it filters on the result of calling strftime() on it. SQLite would have to run that function on every single row just to check, so it can't use the index's sort order at all.

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE strftime('%Y', OrderDate) = '2020';

SCAN AllOrders — the index sits there unused.

Same intent — "orders from 2020" — but expressed as a direct range on the actual column. Nothing hides the column from the planner now.

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE OrderDate >= '2020-01-01' AND OrderDate < '2021-01-01';

SEARCH AllOrders USING INDEX idx_allorders_orderdate (OrderDate>? AND OrderDate<?) — the exact same question, answered through the index.

The index on OrderDate finds the right rows, but this query also needs Status — a column the index doesn't include. SQLite still has to visit the actual table row for that piece, on top of the index lookup.

SELECT OrderDate, Status FROM AllOrders
WHERE OrderDate >= '2020-06-01' AND OrderDate < '2020-07-01';

SEARCH AllOrders USING INDEX idx_allorders_orderdate (OrderDate>? AND OrderDate<?) — a real index search, but not the fastest version of one.

A covering index includes every column a specific query needs, not just the one it filters on. Once OrderDate and Status both live in the index, SQLite can answer the whole query from the index alone.

CREATE INDEX idx_allorders_covering ON AllOrders (OrderDate, Status);

EXPLAIN QUERY PLAN
SELECT OrderDate, Status FROM AllOrders
WHERE OrderDate >= '2020-06-01' AND OrderDate < '2020-07-01';

SEARCH AllOrders USING COVERING INDEX idx_allorders_covering — no trip back to the table at all.

A leading wildcard (%elivered) can never use a sorted index — there's no fixed starting point to jump to. A trailing wildcard (Deliv%) looks like it should work the same way a range does — but SQLite's LIKE is case-insensitive by default, and a case-insensitive match can't be proven safe against a case-sensitive sorted index, so it falls back to a scan too.

CREATE INDEX idx_allorders_status ON AllOrders (Status);

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE Status LIKE '%elivered';

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE Status LIKE 'Deliv%';

Both queries return SCAN AllOrders — for two genuinely different reasons.

GLOB uses the same kind of prefix pattern as LIKE, but it's always case-sensitive — so SQLite can safely treat it as a range against the sorted index, with no ambiguity to rule out.

EXPLAIN QUERY PLAN
SELECT * FROM AllOrders WHERE Status GLOB 'Deliv*';

SEARCH AllOrders USING INDEX idx_allorders_status (Status>? AND Status<?) — the identical prefix pattern, but through GLOB instead of LIKE, actually reaches the index.