MENU
Query Rewriting
Query rewriting is changing a query's text – without changing its result – into a form the optimizer can execute more cheaply. Where Index Strategy covers building the right index, this page covers writing the query so an existing index can actually be used.Avoid SELECT *
SELECT * forces MySQL to fetch every column, which usually rules out a covering index (see Index Strategy) and increases network and buffer traffic for columns the application never reads. It's also fragile: adding a column later silently changes the result set of every SELECT * caller. Name only the columns actually needed:| -- avoid SELECT * FROM orders WHERE customer_id = 42; -- prefer SELECT id, status, created_at FROM orders WHERE customer_id = 42; |
The second form can be satisfied entirely from a covering index; the first cannot.
Sargable Predicates
A predicate is sargable (Search ARGument ABLE) when the optimizer can use it directly against an index without first evaluating a function on every row. Wrapping an indexed column in a function makes the predicate non-sargable – the index can no longer be range-scanned, even if one exists on the column:| -- not sargable: index on created_at cannot be used for a range scan
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024; -- sargable: rewritten as a range, uses an index on created_at EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'; |
Both return the same rows. The first must compute YEAR(created_at) for every row before it can compare it to 2024 – a full scan. The second bounds created_at directly, so the optimizer can seek into the index and scan only the matching range.
The same trap applies to arithmetic (price * 1.1 > 100 instead of price > 100 / 1.1), string functions (UPPER(email) = 'X' instead of storing/comparing a normalized column), and implicit type conversion (comparing a string column to a numeric literal, or vice versa) – in each case, move the computation to the constant side of the comparison, or to the application, and leave the indexed column bare. When a function on the column is unavoidable, a functional index on that exact expression (see Index Strategy) restores sargability.
EXISTS vs IN vs JOIN
All three can express "rows in A that have a match in B," but the optimizer doesn't always treat them identically:| EXISTS | A correlated subquery that stops at the first match per outer row. Good when B is large but only presence/absence matters, and when B may have duplicate matches per outer row (duplicates don't inflate the result, unlike a JOIN). |
| IN (subquery) | Since MySQL 5.6/8.0 the optimizer typically semi-join transforms this into something plan-equivalent to EXISTS or a join, provided the subquery is uncorrelated and simple; check with EXPLAIN rather than assuming. IN with a large static list is fine; NOT IN with a subquery that can return NULL is a classic correctness trap – it can silently return zero rows. |
| JOIN | Needed when columns from both tables appear in the SELECT list. Prefer an INNER JOIN over EXISTS only when you actually need B's columns; otherwise the extra columns (and potential row duplication from a one-to-many match) are wasted work. |
| -- want: customers with at least one shipped order (no order columns needed) SELECT c.id, c.name FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'SHIPPED' ); |
EXISTS stops scanning orders for a customer as soon as one match is found, and never risks duplicating customer rows the way a JOIN would if a customer has multiple shipped orders.
For all three forms, prefer NOT EXISTS over NOT IN (subquery) when checking absence – it has no NULL pitfall and its access pattern is the same as EXISTS.
LIMIT/OFFSET Pagination and the Keyset Fix
| SELECT id, status FROM orders ORDER BY id LIMIT 20 OFFSET 100000; |
MySQL must still generate and discard the first 100,000 matching rows before returning the next 20 – cost grows with the offset, not with the page size. Deep pages become progressively slower.
The fix is keyset (a.k.a. seek) pagination: remember the last row's ordering key from the previous page, and ask for rows strictly after it instead of skipping a row count:
| -- page 1 SELECT id, status FROM orders ORDER BY id LIMIT 20; -- suppose the last id returned was 1042 -- next page: seek past it directly, no OFFSET SELECT id, status FROM orders WHERE id > 1042 ORDER BY id LIMIT 20; |
With an index on id (or the ordering column), each page costs the same regardless of how deep into the result set it is – the index seek jumps straight to the boundary instead of scanning past every prior row.
Keyset pagination requires a unique, indexed, totally-ordered key (append the primary key to break ties when ordering by a non-unique column) and doesn't support jumping to an arbitrary page number – only "next"/"previous" from a known position. For a UI that needs numbered pages deep into a large result set, consider capping how far a user can page, or precomputing/caching page boundaries instead of paying the OFFSET cost on every request.
For the query plans these rewrites produce, see EXPLAIN and Execution Plans.
CREATE TABLE orders (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
status VARCHAR(20) NOT NULL,
created_at DATETIME NOT NULL,
KEY idx_created_at (created_at)
) ENGINE=InnoDB;
-- Not sargable: full scan, YEAR() computed per row
EXPLAIN SELECT * FROM orders WHERE YEAR(created_at) = 2024;
-- Sargable: rewritten as a range, uses idx_created_at
EXPLAIN SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
-- EXISTS vs IN vs JOIN for "has at least one shipped order"
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100));
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id AND o.status = 'SHIPPED'
);-- OFFSET pagination: cost grows with the offset
SELECT id, status FROM orders ORDER BY id LIMIT 20 OFFSET 100000;
-- Keyset (seek) pagination: cost stays flat per page
-- page 1
SELECT id, status FROM orders ORDER BY id LIMIT 20;
-- client remembers last id from the page, e.g. 1042
-- page 2: seek past the last key instead of skipping rows
SELECT id, status FROM orders WHERE id > 1042 ORDER BY id LIMIT 20;