MySQL Optimization Beyond EXPLAIN: Safer Pagination, Keyset Cursors, and Covering Indexes That Actually Stick

Pictalingo

·

MySQL Optimization Beyond EXPLAIN: Safer Pagination, Keyset Cursors, and Covering Indexes That Actually Stick

Most “slow query” fixes start with EXPLAIN and indexes (and you should do that). But a lot of real-world slowness shows up in patterns that look reasonable—like LIMIT/OFFSET pagination, sorting on columns without the right index order, and selecting wide rows when you only need a few fields.

This hands-on guide focuses on practical changes junior/mid developers can apply quickly: keyset pagination (a.k.a. cursor pagination), building covering indexes for common list pages, and avoiding “hidden work” that happens when MySQL has to sort or scan more than you expect.

1) Why LIMIT/OFFSET Gets Slower Over Time

It’s common to paginate like this:

-- page 101, 20 items per page SELECT id, title, created_at FROM posts WHERE status = 'published' ORDER BY created_at DESC LIMIT 20 OFFSET 2000;

Problem: MySQL still needs to walk past the first 2000 rows (in the sorted order) before returning the 20 you want. As your table grows (or the user jumps to deep pages), your query may get noticeably slower.

  • OFFSET cost grows linearly with the offset value.
  • If the sort can’t use an index well, MySQL may create a temporary table and perform a filesort.

Better: use keyset pagination, which lets MySQL jump directly to the next “cursor” value.

2) Keyset Pagination (Cursor Pagination) for List Pages

Keyset pagination uses the last row of the previous page as a cursor. For a feed sorted by created_at DESC, you request “the next 20 rows older than the last item I saw”.

First page:

SELECT id, title, created_at FROM posts WHERE status = 'published' ORDER BY created_at DESC, id DESC LIMIT 20;

Next page (using the last row’s (created_at, id) as the cursor):

-- Suppose the last row from previous page was: -- created_at = '2026-02-10 12:30:00', id = 4812 SELECT id, title, created_at FROM posts WHERE status = 'published' AND (created_at, id) < ('2026-02-10 12:30:00', 4812) ORDER BY created_at DESC, id DESC LIMIT 20;

Notice the tie-breaker: adding id ensures a deterministic order when multiple rows share the same created_at. Without it, you can miss rows or duplicate them between pages.

3) Add the Right Composite Index (Order Matters)

Keyset pagination is only fast if MySQL can traverse an index in the same order as your query. For the queries above, a good index is:

CREATE INDEX idx_posts_status_created_id ON posts (status, created_at DESC, id DESC);

Why this shape?

  • status is used in the WHERE clause.
  • created_at and id match the ORDER BY direction.
  • The cursor condition (created_at, id) < (...) can use the index efficiently.

Tip: In MySQL 8, you can specify descending index order. Even when you don’t, MySQL can sometimes scan an index backwards, but aligning index order explicitly makes intent clear and often helps.

4) Make It a Covering Index (Fewer Reads, Faster Lists)

List pages typically show a few columns (id, title, timestamps, maybe author_id). If MySQL can answer the query using only the index (without reading the table rows), you get a big speed win. That’s called a covering index.

Example: list page shows id, title, created_at. Add title to the index:

CREATE INDEX idx_posts_list_cover ON posts (status, created_at DESC, id DESC, title);

Now the engine can often return id, title, created_at directly from the index leaf pages (especially with InnoDB), reducing random I/O.

  • Covering indexes help most for “feed/list” queries that run constantly.
  • Don’t overdo it: wider indexes cost more disk and slow writes.

5) The “Two-Step” Pattern for Wide Rows (Avoid Fetching Big Text Early)

Another sneaky slowdown: selecting wide rows (large TEXT, JSON blobs, big columns) in list queries. Even if you only show a snippet, pulling full payloads can hurt.

A solid pattern is:

  • Step 1: fetch only IDs and lightweight fields using a covering index.
  • Step 2: fetch full rows by IDs for the 20 items you actually display.

Step 1 (fast):

SELECT id, created_at FROM posts WHERE status='published' AND (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20;

Step 2 (fetch the page’s rows):

SELECT id, title, body, author_id, created_at FROM posts WHERE id IN (....20 ids....);

In application code, keep the original ordering by sorting results according to the ID list you fetched in step 1 (or use a FIELD(id, ...) trick if you must).

6) Working Example in PHP (Laravel-Style, No Magic)

Here’s a practical keyset pagination function using PDO. It returns items plus a cursor you can send back as next_cursor.

<?php function listPosts(PDO $pdo, ?string $cursorCreatedAt, ?int $cursorId, int $limit = 20): array { $sql = " SELECT id, title, created_at FROM posts WHERE status = 'published' "; $params = []; if ($cursorCreatedAt !== null && $cursorId !== null) { $sql .= " AND (created_at, id) < (?, ?)"; $params[] = $cursorCreatedAt; $params[] = $cursorId; } $sql .= " ORDER BY created_at DESC, id DESC LIMIT ?"; $params[] = $limit; $stmt = $pdo->prepare($sql); $stmt->execute($params); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); $nextCursor = null; if (count($rows) > 0) { $last = $rows[count($rows) - 1]; $nextCursor = [ 'created_at' => $last['created_at'], 'id' => (int)$last['id'], ]; } return [ 'items' => $rows, 'next_cursor' => $nextCursor, ]; }

Client-side, you store next_cursor and pass it back on “Load more”. This approach scales much better than deep offsets.

7) Sorting + Filtering: Build Indexes That Match Real Screens

List screens often filter by status, author_id, category_id, or a time range, then sort by created_at. The best index is usually a composite that matches:

  • Equality filters first (=), like status, author_id
  • Then the sort keys (created_at, id)

Example: “My Posts” page:

CREATE INDEX idx_posts_author_status_created_id ON posts (author_id, status, created_at DESC, id DESC);

Then your query becomes:

SELECT id, title, created_at FROM posts WHERE author_id = ? AND status = 'published' AND (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20;

Don’t guess indexes in a vacuum—design them around your top 5–10 most-used queries (feeds, dashboards, admin lists).

8) Prevent “Hidden Sorts” with Stable Ordering

If you sort by a non-unique column like created_at, add a unique tie-breaker (id) everywhere—especially for pagination. Otherwise:

  • Two requests can return overlapping rows if new posts appear between calls.
  • Rows with identical timestamps can shuffle order, causing duplicates/missing items.

Rule of thumb: for pagination queries, always use ORDER BY created_at DESC, id DESC (or the equivalent for your sort).

9) Quick Checklist You Can Apply Today

  • Replace deep OFFSET pagination with keyset pagination.
  • Add a deterministic tie-breaker column (id) to ORDER BY.
  • Create composite indexes that match your WHERE + ORDER BY pattern.
  • Turn hot list queries into covering-index queries by including just the needed columns.
  • For wide rows, fetch IDs first, then fetch full records for only the current page.
  • Keep indexes focused: optimize the few queries that run constantly.

10) Minimal Schema + Query Demo (You Can Copy/Paste)

Here’s a compact example you can run in a dev database:

CREATE TABLE posts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, status VARCHAR(20) NOT NULL, title VARCHAR(255) NOT NULL, body TEXT NOT NULL, created_at DATETIME NOT NULL, KEY idx_posts_status_created_id (status, created_at DESC, id DESC), KEY idx_posts_list_cover (status, created_at DESC, id DESC, title) ) ENGINE=InnoDB; -- First page SELECT id, title, created_at FROM posts WHERE status='published' ORDER BY created_at DESC, id DESC LIMIT 20; -- Next page (cursor: last created_at + id from previous page) SELECT id, title, created_at FROM posts WHERE status='published' AND (created_at, id) < ('2026-02-10 12:30:00', 4812) ORDER BY created_at DESC, id DESC LIMIT 20;

If your app has a feed, an admin list, or any “infinite scroll” screen, these changes usually give the most noticeable win per hour spent. And unlike one-off tuning, this kind of optimization holds up as your data grows.

Leave a Reply

Your email address will not be published. Required fields are marked *