Skip to content
Tagged articles

Delayed Join

4 articles · Page 1 of 1
Java Tech Workshop
Java Tech Workshop
Oct 29, 2026 · Databases

MySQL Deep Pagination: Why OFFSET 99980 Takes 4.68s and How to Cut It to 6ms

This article analyzes why MySQL LIMIT with large offsets causes severe performance degradation, explaining the server-layer mechanics of OFFSET, comparing index-scan vs full-table-scan execution paths, and demonstrating two optimization techniques — delayed join (covering index subquery) and cursor-based pagination — with benchmark results showing 4.68s reduced to 0.21s and 0.006s respectively on a 1.28M-row table.

Covering IndexCursor PaginationDeep Pagination
0 likes · 17 min read
MySQL Deep Pagination: Why OFFSET 99980 Takes 4.68s and How to Cut It to 6ms
ITPUB
ITPUB
Nov 12, 2022 · Databases

How to Speed Up Large Pagination Queries in MySQL: Delayed Join and Bookmark Techniques

This article explains why traditional ORDER BY … LIMIT pagination becomes slow on tens of millions of rows, analyzes the underlying index scan cost, and presents two practical optimizations—delayed join using covering indexes and a bookmark‑based approach—showing how they can cut execution time to a third or even a tenth of the original.

Delayed JoinPaginationbookmark pagination
0 likes · 8 min read
How to Speed Up Large Pagination Queries in MySQL: Delayed Join and Bookmark Techniques
Programmer DD
Programmer DD
May 26, 2019 · Databases

How to Speed Up Large‑Scale MySQL Pagination: Practical Tips & Benchmarks

This article explains why traditional MySQL pagination (ORDER BY … LIMIT N,M) becomes painfully slow on tens of millions of rows, analyzes the root causes, and presents concrete optimizations such as covering‑index delayed joins, bookmark‑based scans, and query rewrites that can cut execution time to a third of the original.

Bookmark ScanCovering IndexDelayed Join
0 likes · 9 min read
How to Speed Up Large‑Scale MySQL Pagination: Practical Tips & Benchmarks
Youzan Coder
Youzan Coder
Nov 16, 2018 · Databases

Optimizing Large-Scale Pagination Queries in MySQL

When paginating millions of rows in MySQL, avoid large OFFSET scans by using delayed joins or bookmark techniques that first fetch primary‑key values via covering indexes, then retrieve the needed rows, dramatically reducing I/O and query latency.

Delayed JoinIndexPagination
0 likes · 10 min read
Optimizing Large-Scale Pagination Queries in MySQL