Query Optimisation and Pagination Performance

The optimisation loop

1. Measure. Find the query from the slow query log, ordered by total time.
2. Explain. Read the plan. Note type, key, rows and Extra.
3. Form one hypothesis. Missing index? Non sargable predicate? Wrong join order? Stale statistics?
4. Change one thing.
5. Re measure. Compare plans and timings on production sized data.

Changing three things at once teaches you nothing, and one of them is often making it worse.

The checklist

1. Return less

SELECT * FROM orders WHERE customer_id = 1;                 -- every column
SELECT id, order_date, total FROM orders WHERE customer_id = 1;  -- what you need

Fewer columns means less I/O, less network, and the possibility of a covering index.

2. Make predicates sargable

WHERE YEAR(order_date) = 2024                          -- index unusable
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'   -- index usable

WHERE UPPER(email) = 'ASHA@EXAMPLE.COM'                -- unusable
WHERE email = 'asha@example.com'                       -- usable

WHERE total * 1.18 > 10000                             -- unusable
WHERE total > 10000 / 1.18                             -- usable

3. Filter early

-- Joins everything, then discards most of it
SELECT c.name, o.total
FROM   customers c JOIN orders o ON o.customer_id = c.id
WHERE  o.order_date >= '2024-01-01';

-- Reduces orders first, then joins the survivors
SELECT c.name, o.total
FROM  (SELECT customer_id, total FROM orders WHERE order_date >= '2024-01-01') o
JOIN   customers c ON c.id = o.customer_id;

Modern optimisers often do this for you. It still helps to write it clearly, and on complex queries it sometimes makes the difference.

4. Aggregate before joining

-- Joins to the many side, then groups - more rows through every step
SELECT c.name, COUNT(o.id), SUM(o.total)
FROM   customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

-- Groups first, then joins one row to one row
SELECT c.name, COALESCE(t.cnt, 0), COALESCE(t.revenue, 0)
FROM   customers c
LEFT JOIN (SELECT customer_id, COUNT(*) AS cnt, SUM(total) AS revenue
           FROM   orders GROUP BY customer_id) t ON t.customer_id = c.id;

5. Remove work that does nothing

• DISTINCT added to hide join duplicates - fix the join instead.
• ORDER BY on a result nobody reads in order.
• Joins to tables whose columns never appear in the output - use EXISTS.
• COUNT(*) on a huge table for a "showing 1-20 of N" label. Show "20+" instead, or cache the count.

Pagination performance

-- Page 1: fast
SELECT id, order_date, total FROM orders
ORDER BY order_date DESC, id DESC LIMIT 20 OFFSET 0;

-- Page 5000: slow. The engine produces 100,020 rows and throws away 100,000.
SELECT id, order_date, total FROM orders
ORDER BY order_date DESC, id DESC LIMIT 20 OFFSET 100000;

OFFSET is not a seek. There is no index structure that lets a B-tree jump to "the hundred thousandth row"; it must be counted to. The cost grows linearly with the offset, and no index removes it.

Keyset pagination

-- Remember the last row of the previous page: (2024-03-19, id 1002)
SELECT id, order_date, total
FROM   orders
WHERE  (order_date, id) < ('2024-03-19', 1002)
ORDER BY order_date DESC, id DESC
LIMIT  20;

Now every page is an index seek plus twenty sequential reads - page 5,000 costs exactly what page 1 costs. The trade off is real and worth stating: you can go forward and backward, but you cannot jump to an arbitrary page number.

The covering index for pagination

CREATE INDEX idx_orders_page ON orders (order_date DESC, id DESC, total);

With the sort columns leading and the selected columns included, each page is answered from the index alone.

Important rules

• Optimise what is actually slow, ordered by total time, not by intuition.
• An index cannot fix a deep OFFSET.
• Always include a unique tie breaker in a paginated ORDER BY.
• Reducing rows examined is nearly always the real fix.
• Measure on production sized data; small tables hide every problem.

Common mistakes

• Adding indexes until something improves, leaving the rest behind forever.
• Using OFFSET for infinite scroll.
• Running COUNT(*) alongside every paginated query.
• Optimising a nightly report while a 40ms query runs ten million times a day.
• Changing several things at once and not knowing which one helped.

Best practices

• Work the checklist in order: return less, make predicates sargable, filter early, aggregate before joining, remove pointless work.
• Use keyset pagination for anything that goes deep.
• Build a covering index for the hot paginated query.
• Cache or approximate total counts rather than computing them per page.
• Keep before and after plans in the pull request; they are the evidence.

Practice

1. Rewrite three non sargable predicates from your own codebase.
2. Convert an OFFSET paginated query to keyset pagination, and say what the client must remember.
3. Design the covering index for ORDER BY order_date DESC, id DESC returning id, date and total.