Tagged articles
LIMIT OFFSET
10 articles · Page 1 of 1
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

This article analyzes the performance bottleneck of LIMIT OFFSET deep pagination in MySQL with millions of rows, demonstrates EXPLAIN plan analysis, compares traditional pagination, subquery, covering index, and keyset pagination approaches, provides Spring Boot implementation examples for MyBatis-Plus and JPA, and benchmarks showing keyset pagination maintains constant response time regardless of page depth.
Cursor PaginationDeep PaginationKeyset Pagination
0 likes · 22 min read

The article analyzes why a MySQL LIMIT OFFSET query on a 9.5 million‑row table takes 16 seconds, demonstrates how moving the filter into a sub‑query that returns only primary‑key IDs and joining back reduces execution to 0.35 seconds, and validates the theory by measuring InnoDB buffer‑pool page accesses.
Buffer PoolInnoDBLIMIT OFFSET
0 likes · 9 min read

This article reproduces a slow‑query scenario caused by deep pagination with LIMIT, explains the underlying index mechanics, and presents several practical SQL techniques—subquery, INNER JOIN, and anchor‑record methods—to dramatically reduce execution time.
Back-tableINNER JOINIndex Optimization
0 likes · 11 min read

GaussDB for MySQL introduces a Limit‑Offset pushdown optimization that moves offset calculation to the InnoDB storage engine, eliminating unnecessary row transfers and index‑back‑table lookups, thereby dramatically reducing query latency for large offsets as demonstrated by TPC‑H benchmarks.
Database PerformanceGaussDBLIMIT OFFSET
0 likes · 13 min read


21CTOAug 16, 2021 · Databases
This article explains why MySQL queries using LIMIT with a large offset become extremely slow, demonstrates the inefficiency through a 950‑million‑row table example, and shows how rewriting the query with a sub‑select join reduces execution time from seconds to milliseconds while preserving results.
Buffer PoolInnoDBLIMIT OFFSET
0 likes · 9 min read

A production MySQL query that uses huge OFFSET values caused massive slowdown, and the article explains the root cause, demonstrates data‑generation scripts, benchmarks the problem, and presents three practical solutions—including index‑covering subqueries, remembering the last primary‑key, and offset throttling—to dramatically improve pagination performance.
LIMIT OFFSETMySQL
0 likes · 15 min read

This article explains how using a large LIMIT offset on a 9.5‑million‑row MySQL table causes massive I/O and slow queries, and demonstrates a sub‑query‑based rewrite that fetches only primary keys before joining, dramatically reducing execution time while also showing buffer‑pool effects.
Buffer PoolIndexLIMIT OFFSET
0 likes · 8 min read

This article demonstrates how to dramatically improve MySQL LIMIT offset query performance by moving the filter into a subquery that selects only primary keys, then joining to retrieve full rows, and validates the approach with buffer pool statistics and execution time measurements.
Buffer PoolLIMIT OFFSETMySQL
0 likes · 9 min read

This article demonstrates how rewriting a MySQL LIMIT OFFSET query to first fetch primary keys in a sub‑query and then join the main table can cut execution time from over 16 seconds to under one second by avoiding massive random I/O and buffer‑pool pollution.
Buffer PoolIndex ScanLIMIT OFFSET
0 likes · 7 min read
