Query Rewrite

Hook: Interviewers love this because a tiny rewrite can turn a full scan of millions of rows into a fast index range scan.

Question: What is query rewrite in SQL, and how does it help performance?

Answer: Query rewrite means changing a SQL statement into an equivalent form that the database can execute more efficiently. The main goal is to keep indexed columns easy to search, push filters earlier, and avoid hiding a column inside a function or expression. In practice, that often changes a slow table scan into a much smaller index seek or index range scan.

Interview-Ready Answer: I use query rewrite to make the same result cheaper to compute. My first check is whether the predicate is sargable, meaning the database can use an index for it, so I try to keep the indexed column on its own and move calculations to the other side. For example, instead of wrapping created_at in a date function, I rewrite it as a half-open time range, which usually turns a full scan into a fast index range scan. I also watch for NULLs and outer joins so I do not accidentally change the result set.

What query rewrite really is

Query rewrite is not magic; it is a deliberate way to express the same logic in a form the optimizer can execute better. A predicate is a filter condition, like a WHERE clause. A sargable predicate is one the database can use with an index instead of reading rows one by one. Think of it as giving the engine a short route instead of asking it to inspect every house on the street.

How it works under the hood

1. The database parses your SQL and normalizes it into an internal form.
2. The optimizer estimates how many rows each filter will match using table statistics and histograms, which are summaries of data distribution.
3. If a predicate is sargable, the optimizer can choose an index seek or index range scan; if not, it may fall back to a full scan.
4. The engine pushes filters down as early as possible so fewer rows flow into joins, sorts, and aggregates.
5. Better row estimates often lead to better join order, which can be the difference between touching thousands of rows and millions.

Common rewrite patterns

Two key ideas are worth remembering: first, rewriting is about shape, not just syntax; second, the optimizer is smart, but it is not psychic. It cannot always infer your business meaning if you hide it inside functions, casts, or awkward joins.

When and why to use it

Use query rewrite when a query is slow because it filters too late, hides an indexed column inside an expression, or forces the engine to read far more rows than needed. Typical examples include date filtering, string matching, existence checks, and join simplification. If the query already uses a good index and reads only a small part of the table, rewriting may not help much, so check the execution plan instead of guessing.

Performance and complexity notes

A full table scan is roughly O(N) because the engine checks many or all rows. A B-tree index lookup is closer to O(log N + K), where K is the number of matched rows. On a table with 10 million rows, reading 5,000 rows from one day is usually far cheaper than scanning the whole table. In real systems, a good rewrite can cut latency from seconds to milliseconds and reduce I/O, CPU, and connection-pool pressure. Also remember selectivity: if a predicate matches 70% of the table, a scan may actually be cheaper than bouncing through the index many times.

Important edge cases

• NULLs: Rewrites must preserve three-valued logic; NULL is not equal to anything, even another NULL.
• Outer joins: Moving a filter from WHERE into ON can change which rows survive.
• OR predicates: Sometimes better as UNION ALL or IN, but only if duplicates and semantics are safe.
• Version differences: Some engines or versions inline CTEs aggressively; older ones may materialize them and block optimization.
• Expression indexes: If you cannot rewrite the query, an index on the expression may be the next best fix.

Memory rule: if the filter is on the indexed column itself, the index has a chance; if the column is wrapped in a function, the index often cannot help.

Real-world story

Imagine an e-commerce checkout service with an endpoint that shows today’s paid orders. A developer writes WHERE DATE(created_at) = CURRENT_DATE because it looks clean. During a sale, the orders table grows to tens of millions of rows, the query starts scanning huge chunks of data, and the API p95 jumps from 40 ms to 3 seconds. The app’s connection pool of 20 gets exhausted, new requests queue up, and users see spinning loaders and timeout errors.

The logs show repeated slow-query warnings, high CPU on the database, and requests timing out while waiting for a connection. The fix is a rewrite to a half-open range on created_at, which keeps the index usable and makes the query touch only the rows for that day. The outage lesson is simple: a query can be logically correct and still be operationally expensive.

The scary part is that the bug is not obvious in code review. The query returns the right rows, but it asks the database to do much more work than necessary. That is exactly why interviewers like this topic: it tests whether you can connect SQL shape to production behavior.

Follow-up & Tricky Questions:

• How do you know a rewrite actually helped? Compare the actual execution plan, rows read, and latency on production-like data. If the engine still scans the same number of rows, the rewrite was mostly cosmetic.
• When is EXISTS better than IN? EXISTS is often better for correlated checks because the engine can stop at the first match. Modern optimizers sometimes transform both into similar plans, so the data shape matters more than the keyword alone.
• Should I rewrite first or add an index first? Rewrite first if the predicate is non-sargable or filtering too late; add an index when the access pattern is stable and selective. A bad query with more indexes is still a bad query.
• Can the optimizer do all rewrites automatically? Not always. It can reorder joins and simplify some expressions, but it will not safely invent business meaning across NULLs, outer joins, or hidden calculations.
• What if the query has many OR conditions? Sometimes you can rewrite to UNION ALL so each branch can use a different index. That only works if duplicate handling and semantics are safe.
• Why is WHERE col + 1 = 5 a gotcha? Because the arithmetic is on the column side, so the index on col is usually harder to use. Rewrite it to col = 4 so the engine can search directly.
• Is BETWEEN always safe for timestamps? Not always. For date-time values, an inclusive end boundary can accidentally include midnight of the next day, so the safer pattern is a half-open range: start inclusive, end exclusive.
• Does rewriting a LEFT JOIN into an INNER JOIN always work? No. It only preserves results when unmatched rows are impossible or irrelevant; otherwise you lose rows that should have been kept with NULLs on the right side.

Common Mistakes:

• Wrapping indexed columns in functions. Fix: move the logic to constants or use an expression index if the rewrite is not possible.
• Forgetting NULL semantics. Fix: test how your rewrite behaves when values are missing, especially with NOT IN and outer joins.
• Assuming every rewrite is faster. Fix: verify with the execution plan and real data; sometimes a scan is cheaper for low-selectivity filters.
• Changing query shape without checking correctness. Fix: confirm that duplicates, join types, and time boundaries still produce the same result.

Memory Hook: Keep the indexed column naked. If you dress it up in a function, the index cannot recognize it quickly.

Cheat Sheet:

• Rewrite to make predicates sargable.
• Prefer range filters over functions on columns.
• Use EXISTS for existence checks when possible.
• Push filters earlier to reduce rows before joins and sorts.
• Check NULLs, outer joins, and boundary times before shipping.
• Always confirm with an execution plan, not just intuition.

Practice Tasks:

• Rewrite a daily report query that uses DATE(created_at) into an index-friendly range filter.
• Convert a COUNT(*) > 0 check into EXISTS and compare the plan.
• Take one slow query from your own app and identify one place where a function, cast, or join order blocks index use.