Why a function on a filtered column can slow SQL

A function around an indexed column can prevent a database from using a straightforward range lookup on that column's ordinary index. Whether it actually does so depends on the engine, expression, available indexes and optimizer. Inspect the plan instead of treating “functions are slow” as a universal rule.

Date filtering is a common example. A predicate that extracts a month from every timestamp may be less convenient for an ordinary timestamp index than an equivalent lower-and-upper-bound range.

Compare two equivalent predicates on controlled data

The advanced SQL lab stores purchase dates as consistently formatted YYYY-MM-DD text. Under that contract, these queries select the same March purchases:

SELECT purchase_id
FROM purchases
WHERE substr(purchased_at, 1, 7) = '2026-03'
ORDER BY purchase_id;

SELECT purchase_id
FROM purchases
WHERE purchased_at >= '2026-03-01' AND purchased_at < '2026-04-01'
ORDER BY purchase_id;

Both return P06, P07 and P08. The range includes the first day of March and excludes the first day of April. Consistent ISO date formatting allows lexical ordering to agree with date ordering in this fixture. Arbitrary mixed-format date strings do not provide that guarantee.

Inspect access with the same index present

Run operational_checks.py in the lab directory. It creates ix_purchases_date on purchased_at and compares the wrapped predicate with the range predicate. The checked SQLite 3.42.0 run uses the date index for a range SEARCH, while the substr predicate scans through the purchase primary-key index.

The script also checks that both queries return the same three IDs. Its saved output records the environment and plans. It makes no runtime-speed claim because eleven rows cannot establish production performance.

The important distinction is between locating a bounded region of indexed values and evaluating an expression while visiting a broader set of rows. Exact plan wording and optimizer choices can change, as documented in SQLite's plan reference.

Preserve the business meaning during a rewrite

Suppose a timestamp is stored in UTC but the report asks for March in India. A UTC March boundary is not the same as a March boundary in India. Derive the corresponding storage-time boundaries from the reporting timezone, then apply them consistently.

For regions with daylight-saving changes, do not assume every local day has 24 hours. Construct local period boundaries using a timezone-aware process and convert each boundary to the storage timezone. A faster query with the wrong reporting window is an incorrect optimization.

Similarly, replacing LOWER(email) with email equality changes case-handling unless the data and collation contract supports the rewrite. Normalization, accent handling and whitespace rules must remain explicit.

Expression indexes can be appropriate

Sometimes the expression is the actual recurring search requirement. An index on that expression may be a valid alternative. SQLite supports expression indexes subject to its restrictions, and the query expression must match in the way its planner recognizes. See SQLite's expression-index documentation.

That option has costs: additional storage, maintenance during writes and a tighter relationship between query formulation and index design. It also does not fix an expression whose semantics are wrong. Evaluate it against representative workloads rather than creating a separate index for every report variation.

A stored normalized field or generated column can sometimes make the contract easier to inspect, depending on database capabilities. Keep the original value when normalization could lose information needed for audit or correction.

Test the boundaries, then measure

Create fixtures exactly at the lower bound, just before the upper bound and exactly at the upper bound. Include NULL and malformed values according to the source contract. Compare complete result keys, not only counts, because two different sets can have equal size.

After correctness is established, measure under realistic volume and selectivity. The query may still be dominated by a join, aggregation or result transfer, so an improved filter access path need not dominate total latency.

Exercise: add an April 1 purchase and a March 31 purchase. Verify the range selects only the latter. Then explain why a textual date stored as 03/04/2026 would make the original fixture's assumptions invalid.

NeuraPath's Data Analytics with Generative AI course connects SQL reporting with practical quality checks. A useful optimization explanation shows semantic equivalence and execution evidence together.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Read EXPLAIN before adding an index.
• Continue with Parameterize Python SQL queries without string interpolation.