WP_Query pagination performance degradation on high offset values

The short answer

WP_Query has no native cursor or keyset argument — paged and posts_per_page always compile to LIMIT offset, count, and nothing in the core API changes that. The recommended strategy for constant-time deep pagination is keyset (seek) pagination implemented through the posts_where filter: instead of telling MySQL "skip 200,000 rows," you tell it "give me rows after the last one I saw," using an indexed sort column plus a unique tiebreaker.

Why offset gets slow (and why your current fixes don't)

Confirmed behavior: with LIMIT 200000, 20, MySQL must locate, read, and discard 200,000 rows before returning 20. Cost grows linearly with page depth. no_found_rows => true removes the separate SQL_CALC_FOUND_ROWS count scan, and fields => 'ids' shrinks memory use — both are worth keeping — but neither touches the offset scan itself, which is the dominant cost on deep pages.

Implementing keyset pagination

Track the sort key of the last row on the current page — typically post_date plus ID as a deterministic tiebreaker — and pass it forward as a cursor (e.g., in the URL or an opaque token). Then inject the seek condition:

add_filter( 'posts_where', function( $where ) {
    $cursor = get_cursor_from_request(); // e.g. ['2026-09-01 12:00:00', 48123]
    if ( $cursor ) {
        global $wpdb;
        $where .= $wpdb->prepare(
            " AND ( {$wpdb->posts}.post_date, {$wpdb->posts}.ID ) < ( %s, %d )",
            $cursor[0], $cursor[1]
        );
    }
    return $where;
});

$query = new WP_Query( [
    'posts_per_page' => 20,
    'orderby'        => [ 'date' => 'DESC', 'ID' => 'DESC' ],
    'no_found_rows'  => true,
    'fields'         => 'ids',
] );

The tuple comparison (post_date, ID) < (?, ?) is essential: filtering on post_date alone will skip or duplicate rows whenever multiple posts share a timestamp. With ORDER BY post_date DESC, ID DESC and a matching index, each page fetch examines roughly posts_per_page rows regardless of depth — constant time instead of linear.

If the filter approach feels fragile, using $wpdb directly for the listing query gives you full control over the SQL and avoids interference from other plugins hooking the same filters.

Trade-offs and caveats

• You give up arbitrary page jumps ("go to page 4,000") and total page counts. The UI becomes next/previous links or infinite scroll. This is usually the right trade for deep archives and feeds.
• The sort must be stable and indexed. If you order by meta_value, the cursor condition can only use an index if a suitable composite index exists — otherwise you may negate the benefit entirely.
• posts_where is version-sensitive and can conflict with plugins that also rewrite query SQL; scope the filter to only this query (add it immediately before, remove it after).
• If you genuinely need random page access on millions of rows, keyset won't help; look at an external search index (Elasticsearch, SearchWP) or a precomputed summary table instead.

Verifying it worked

1. Enable Query Monitor or SAVEQUERIES and confirm the generated SQL contains the cursor WHERE clause and no large OFFSET.
2. Run EXPLAIN on a shallow page and a deep page; rows examined should be roughly equal with keyset, versus growing with offset before the change.
3. On a production-sized copy, benchmark page 1 vs. page 10,000 response time before and after.

One caveat on the index assumption: the examples above assume a standard wp_posts schema where post_date ordering can use an index; if your table is heavily customized, check EXPLAIN output for the actual index used before rolling this out.