runtime

query performance compare approaches · real SQLite plans

Query Performance

Compare two ways of writing the same query and inspect the real SQLite execution plan for each.

Function filter vs date range

Both queries list November 2010 invoices. Why can only one of them jump straight to November?

how it works

orders has an index on ordered_at. Query A wraps the column in strftime(), so SQLite cannot compare the index entries to '2010-11' and walks the whole index, computing strftime() for every one of the 6,722 orders. Query B compares the raw column to a half-open range, so SQLite seeks to the first November entry and stops at December.

what the plans show

What to notice: Keep the indexed column bare in WHERE. Express the condition on the column (a range) rather than on a function of the column.

tables involved

  • orders 6,722 rows

Both approaches run on a fresh copy of the full Retail dataset. Browser timings are noisy, so the plan is the evidence, not the milliseconds.

how to read a query plan

SCAN reads every row, or every entry of an index. SEARCH jumps to just the matching entries of an index; the terms in brackets show which columns the jump used. COVERING INDEX means the index alone answers the query and the table is not read. USE TEMP B-TREE is an extra step where SQLite sorts or groups rows itself. An index does not make every query faster: it helps when it lets SQLite skip work, and it costs space and slower writes.

a.sql · strftime() on the column
sql›
SELECT order_id, invoice_no, ordered_at
FROM orders
WHERE strftime('%Y-%m', ordered_at) = '2010-11'
ORDER BY ordered_at, order_id;

waiting for the comparison

b.sql · half-open date range
sql›
SELECT order_id, invoice_no, ordered_at
FROM orders
WHERE ordered_at >= '2010-11-01' AND ordered_at < '2010-12-01'
ORDER BY ordered_at, order_id;

waiting for the comparison

SQL Query Performance · Runtime