9.4 Why a Function on a Date Column Can Be Slow Later (Habit for Bigger Databases)

By Ravindra Bagale Last updated: 31 October 2026

Why. A query that is fast on 25 rows can be very slow on 25 million rows. Good habits now save you later.

What. An index is like the index at the back of a book. It helps the database jump to matching rows. A plain range like posted_at >= '2026-01-01' can use an index on posted_at, but strftime('%Y', posted_at) = '2026' usually cannot, because the function must run on every row first. SQLite has no YEAR() function, so strftime() plays that role here.

How. Write the same year filter in two ways and confirm that both give the same answer. Prefer the range version.

Steps

Run the first example, which wraps the column in strftime().
Run the second example, which uses a plain range.
Check that both counts are the same.
Use the range style in shared reports, because it stays fast when the table grows.

Examples

Example 1 · dataset: northstar_bank

SELECT COUNT(*) AS rows_2026
FROM transactions
WHERE strftime('%Y', posted_at) = '2026';

Output

rows_2026
25

Meaning. All 25 rows are from 2026. The answer is right, but the function runs on every row.

Example 2 · dataset: northstar_bank

SELECT COUNT(*) AS rows_2026
FROM transactions
WHERE posted_at >= '2026-01-01'
  AND posted_at <  '2027-01-01';

Output

rows_2026
25

Meaning. The range gives the same 25 rows, and it can use an index on posted_at in a big table.

Ravindra Bagale's Tip

Keep the column alone on one side of the comparison, and put the calculation on the value side.