Query tuning: sargable predicates, keyset pagination and plan trees

Tuning starts with a number. EXPLAIN gives estimates; the server's handler counters (Handler_read_* in performance_schema.session_status) count how many index entries and rows the storage engine was actually asked to read. The examples below run each query through a small measure helper that reads those counters before and after, and subtracts the helper's own overhead. The table holds 4,000 orders, one per hour from 1 January 2026, with indexes on created_at and email.

Sargable predicates

A predicate is sargable (Search ARGument ABLE) when the index can be searched for it: the bare column compared with something that does not depend on the row.

1CREATE TABLE orders (2  id          INT AUTO_INCREMENT PRIMARY KEY,3  customer_id INT NOT NULL,4  email       VARCHAR(50) NOT NULL,5  created_at  DATETIME NOT NULL,6  KEY idx_created (created_at),7  KEY idx_email (email)8);9SET SESSION cte_max_recursion_depth = 5000;10INSERT INTO orders (customer_id, email, created_at)11WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 4000)12SELECT n % 400, CONCAT('user', n, '@example.com'), '2026-01-01' + INTERVAL n HOUR FROM s;13ANALYZE TABLE orders;14-- Helper: measure(query) records how many Handler_read_* operations the query caused.15CREATE TABLE measurements (n INT AUTO_INCREMENT PRIMARY KEY, query TEXT, handler_reads BIGINT);16CREATE PROCEDURE run_counted(IN q TEXT, OUT n_reads BIGINT)17BEGIN18  SELECT SUM(VARIABLE_VALUE) INTO @before FROM performance_schema.session_status19   WHERE VARIABLE_NAME LIKE 'Handler\_read\_%';20  SET @sql = q;21  PREPARE s FROM @sql;22  EXECUTE s;23  DEALLOCATE PREPARE s;24  SELECT SUM(VARIABLE_VALUE) INTO @after FROM performance_schema.session_status25   WHERE VARIABLE_NAME LIKE 'Handler\_read\_%';26  SET n_reads = @after - @before;27END;28CREATE PROCEDURE measure(IN q TEXT)29BEGIN30  CALL run_counted('DO 0', @baseline);31  CALL run_counted(q, @n_reads);32  INSERT INTO measurements (query, handler_reads) VALUES (q, @n_reads - @baseline);33END;

1CALL measure('SELECT COUNT(*) FROM orders WHERE YEAR(created_at) = 2026 AND MONTH(created_at) = 3');2CALL measure('SELECT COUNT(*) FROM orders WHERE created_at >= ''2026-03-01'' AND created_at < ''2026-04-01''');3CALL measure('SELECT COUNT(*) FROM orders WHERE DATE(created_at) = ''2026-03-15''');4CALL measure('SELECT COUNT(*) FROM orders WHERE created_at >= ''2026-03-15'' AND created_at < ''2026-03-16''');5CALL measure('SELECT id FROM orders WHERE email LIKE ''user123%''');6CALL measure('SELECT id FROM orders WHERE email LIKE ''%123@example.com''');7 8SELECT n, query, handler_reads FROM measurements ORDER BY n;

• YEAR(created_at) = 2026 AND MONTH(created_at) = 3 has to compute the functions for every row: 4,002 reads for 744 matches. The half-open range asks for the same rows and reads 745: every match plus one entry to find the end.
• DATE(created_at) = '2026-03-15' versus the one-day range: 4,002 reads versus 25.
• LIKE 'user123%' is a range on the index (a prefix), 12 reads. LIKE '%123@example.com' has no prefix to search for: 4,002.

Other rewrites with the same idea: amount * 1.2 > 100 becomes amount > 100 / 1.2; a string column compared with a number gets quoted (data-types lesson); a collation mismatch gets fixed in the schema.

Pagination: OFFSET versus keyset

LIMIT 10 OFFSET 3000 does not jump to row 3,001. MySQL reads and throws away the first 3,000 rows, so page 300 is 300 times the work of page 1. Keyset pagination remembers where the previous page ended and seeks straight there:

-- next page after the last row shown, ordered by (created_at, id)
WHERE (created_at, id) > ('2026-03-02 10:00:00', 1234)
ORDER BY created_at, id
LIMIT 10

1CREATE TABLE orders (2  id          INT AUTO_INCREMENT PRIMARY KEY,3  customer_id INT NOT NULL,4  created_at  DATETIME NOT NULL5);6SET SESSION cte_max_recursion_depth = 5000;7INSERT INTO orders (customer_id, created_at)8WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 4000)9SELECT n % 400, '2026-01-01' + INTERVAL n HOUR FROM s;10ANALYZE TABLE orders;11-- Helper: measure(query) records how many Handler_read_* operations the query caused.12CREATE TABLE measurements (n INT AUTO_INCREMENT PRIMARY KEY, query TEXT, handler_reads BIGINT);13CREATE PROCEDURE run_counted(IN q TEXT, OUT n_reads BIGINT)14BEGIN15  SELECT SUM(VARIABLE_VALUE) INTO @before FROM performance_schema.session_status16   WHERE VARIABLE_NAME LIKE 'Handler\_read\_%';17  SET @sql = q;18  PREPARE s FROM @sql;19  EXECUTE s;20  DEALLOCATE PREPARE s;21  SELECT SUM(VARIABLE_VALUE) INTO @after FROM performance_schema.session_status22   WHERE VARIABLE_NAME LIKE 'Handler\_read\_%';23  SET n_reads = @after - @before;24END;25CREATE PROCEDURE measure(IN q TEXT)26BEGIN27  CALL run_counted('DO 0', @baseline);28  CALL run_counted(q, @n_reads);29  INSERT INTO measurements (query, handler_reads) VALUES (q, @n_reads - @baseline);30END;

1CALL measure('SELECT id, created_at FROM orders ORDER BY id LIMIT 10 OFFSET 3000');2CALL measure('SELECT id, created_at FROM orders WHERE id > 3000 ORDER BY id LIMIT 10');3 4SELECT n, query, handler_reads FROM measurements ORDER BY n;

3,011 reads versus 10. The sort key must be unique (add id as a tiebreaker), and the trade-off is that you can step to the next or previous page but not jump to page 300. Stepping back is the same seek in the other direction: compare with < from the first row of the current page and read in descending order, which hands you the rows nearest the cursor first.

Reading EXPLAIN FORMAT=TREE

EXPLAIN FORMAT=TREE prints the plan as nested operations. Read it from the most indented line outwards: inner lines produce rows for the line above them.

1CREATE TABLE customers (id INT PRIMARY KEY, country CHAR(2) NOT NULL, KEY idx_country (country));2CREATE TABLE orders (3  id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, amount INT NOT NULL,4  KEY idx_customer (customer_id)5);6SET SESSION cte_max_recursion_depth = 5000;7INSERT INTO customers8WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 400)9SELECT n, ELT(1 + n % 4, 'GE', 'DE', 'PL', 'FR') FROM s;10INSERT INTO orders (customer_id, amount)11WITH RECURSIVE s (n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 4000)12SELECT 1 + n % 400, n % 97 FROM s;13ANALYZE TABLE customers, orders;

1EXPLAIN FORMAT=TREE2SELECT c.country, SUM(o.amount) AS total3FROM customers c4JOIN orders o ON o.customer_id = c.id5WHERE c.country = 'GE'6GROUP BY c.country;

Bottom-up: a covering lookup on idx_country finds the Georgian customers (estimate 100); for each one, an index lookup on idx_customer finds that customer's orders (estimate 10 each); the nested loop joins them (1,000 rows) and the group aggregate sums them. cost is the optimizer's unit-less estimate and rows its row estimate. EXPLAIN ANALYZE runs the query and prints the same tree with actual rows, loops and timings next to each estimate, which is the fastest way to find where an estimate went wrong.

Common mistakes

• Wrapping the indexed column in a function (DATE(), YEAR(), LOWER()), which reads every row: 4,002 reads instead of 25.
• Deep OFFSET pagination on large tables. The cost grows with the page number; 3,011 reads to return 10 rows.
• Keyset pagination on a non-unique key. WHERE created_at > ? skips rows that share the last timestamp; compare (created_at, id).
• Tuning from estimates alone. EXPLAIN's rows is a guess; measure, or use EXPLAIN ANALYZE.

What interviewers ask