Skip to content
Tagged articles

Deep Pagination

23 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
Internet Backend Architecture Stack
Internet Backend Architecture Stack
Oct 10, 2026 · Backend Development

Elasticsearch for E-Commerce: Index Design, Deep Pagination, Split-Brain & Data Sync

This article distills production-hardened Elasticsearch patterns from a billion-item e-commerce search system, covering index mapping pitfalls, IK analyzer tuning, search_after pagination, split-brain prevention, hot-warm-cold ILM tiering, dual-path MySQL-ES sync via Canal and MQ, and a 7-point architecture review checklist.

CanalCluster ArchitectureConsistency Guarantee
0 likes · 25 min read
Elasticsearch for E-Commerce: Index Design, Deep Pagination, Split-Brain & Data Sync
Xiaolin Talks Programming
Xiaolin Talks Programming
Aug 27, 2026 · Backend Development

Why Your Deep Pagination Is Slow: LIMIT OFFSET vs Keyset Pagination in Spring Boot

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
Why Your Deep Pagination Is Slow: LIMIT OFFSET vs Keyset Pagination in Spring Boot
LuTiao Programming
LuTiao Programming
Jul 22, 2026 · Backend Development

Why Does Pagination Slow Down at 10 Million Records? Rethinking MySQL LIMIT in Java Backends

The article explains why deep pagination with large offsets becomes increasingly slow, why adding indexes often does not help, examines common “optimizations” like delayed joins, introduces cursor (keyset) pagination as a high‑performance alternative, and provides practical guidelines for designing pagination APIs in Java back‑ends at scale.

Cursor PaginationDeep PaginationJava
0 likes · 16 min read
Why Does Pagination Slow Down at 10 Million Records? Rethinking MySQL LIMIT in Java Backends
ITPUB
ITPUB
Jul 17, 2026 · Backend Development

Cutting 50 M‑record Deep Paging from 10 min to 1 s – 600× Faster with ES Search‑After & Redis

This article details how a photo‑contest backend migrated from MySQL to Elasticsearch and, through three rounds of optimization—including multi‑level Redis anchor caching, recent‑anchor positioning, and a large‑interval‑plus‑small‑page‑anchor strategy—reduced arbitrary deep‑page response time from ten minutes to about one second, achieving a 600‑fold speedup while exposing remaining data‑drift challenges.

CachingDeep PaginationElasticsearch
0 likes · 14 min read
Cutting 50 M‑record Deep Paging from 10 min to 1 s – 600× Faster with ES Search‑After & Redis
vivo Internet Technology
vivo Internet Technology
Jul 1, 2026 · Backend Development

From 10 Minutes to 1 Second: Three‑Stage Elasticsearch Deep‑Pagination Jump Optimization

This article details how a photo‑contest backend migrated from MySQL to Elasticsearch and, through three iterative optimizations—segment pre‑warming, recent‑anchor positioning with Redis ZSet, and a large‑region‑plus‑small‑page cache—reduced arbitrary deep‑page response time on 500 k records from ten minutes to under one second.

Deep PaginationElasticsearchRedis cache
0 likes · 15 min read
From 10 Minutes to 1 Second: Three‑Stage Elasticsearch Deep‑Pagination Jump Optimization
Xiaolin Talks Programming
Xiaolin Talks Programming
Jun 25, 2026 · Backend Development

Building a Production-Grade Search Engine with Spring Boot & Elasticsearch

This article details how to replace MySQL complex queries with Elasticsearch in production, covering data sync via Canal/Kafka, mapping design with IK analyzer, Spring Data ES query builders, deep pagination with Search After, and consistency guarantees through reconciliation and dead-letter queues.

Consistency PatternsDeep PaginationElasticsearch
0 likes · 21 min read
Building a Production-Grade Search Engine with Spring Boot & Elasticsearch
Architecture & Thinking
Architecture & Thinking
Mar 13, 2026 · Databases

Why MySQL Deep Pagination Slows Down Your E‑commerce Site and How to Fix It

The article explains how deep pagination on massive MySQL tables causes full‑table scans, massive I/O, and memory pressure, then presents six concrete optimization techniques—including delayed join, cursor pagination, covering indexes, ID‑range pagination, caching, and partitioning—backed by a real‑world e‑commerce case study and detailed execution‑plan analysis.

Deep PaginationMySQLSQL
0 likes · 18 min read
Why MySQL Deep Pagination Slows Down Your E‑commerce Site and How to Fix It
Architect
Architect
Nov 27, 2025 · Databases

How to Overcome Deep Pagination Bottlenecks with Cursor, Subqueries, and Elasticsearch Search After

This article explains why deep pagination hurts performance in large datasets, demonstrates the inefficiency of offset‑based SQL queries, and presents practical alternatives such as cursor pagination, sub‑query optimizations, delayed joins, caching, business limits, and the Elasticsearch Search After technique for stable, scalable paging.

Cursor PaginationDeep PaginationElasticsearch
0 likes · 10 min read
How to Overcome Deep Pagination Bottlenecks with Cursor, Subqueries, and Elasticsearch Search After
Architecture Digest
Architecture Digest
Nov 5, 2025 · Backend Development

Why Deep Pagination Breaks Elasticsearch and How to Fix It

This article explains how deep pagination in Elasticsearch triggers costly scatter‑gather queries that overload CPU, memory, and network, and it presents practical alternatives such as the scroll API and search_after to achieve efficient, real‑time pagination for large datasets.

Deep PaginationElasticsearchSearch After
0 likes · 10 min read
Why Deep Pagination Breaks Elasticsearch and How to Fix It
Su San Talks Tech
Su San Talks Tech
Mar 7, 2025 · Databases

How to Fix MySQL Deep Pagination Bottlenecks with 6 Proven Techniques

This article dissects the performance problems of MySQL deep pagination, explains why OFFSET‑based queries become increasingly costly, and presents six practical optimization strategies—including cursor pagination, deferred joins, covering indexes, partitioning, precomputed pages, and Elasticsearch integration—to dramatically speed up order‑lookup queries in large e‑commerce databases.

Covering IndexCursor PaginationDeep Pagination
0 likes · 9 min read
How to Fix MySQL Deep Pagination Bottlenecks with 6 Proven Techniques
Java Backend Technology
Java Backend Technology
Sep 25, 2024 · Databases

How to Safely Update Billion‑Row MySQL Tables Without Overloading Binlog

This article explains why a simple full‑table UPDATE on massive MySQL tables can cripple master‑slave replication, analyzes deep‑pagination inefficiencies, and presents a step‑by‑step batch‑update strategy using NO_CACHE and FORCE INDEX to keep binlog size and buffer‑pool impact under control.

Deep PaginationFull Table UpdateMySQL
0 likes · 9 min read
How to Safely Update Billion‑Row MySQL Tables Without Overloading Binlog
Lobster Programming
Lobster Programming
Apr 28, 2024 · Databases

How to Solve MySQL Deep Pagination: Causes and 6 Effective Optimization Strategies

Deep pagination in MySQL becomes a performance bottleneck when tables reach millions of rows, causing excessive row scans and costly index lookups; this article explains its root causes and presents six practical optimization techniques—including keyset pagination, delayed joins, covering indexes, data partitioning, and external search engines—to dramatically improve query speed.

Covering IndexDeep PaginationKeyset Pagination
0 likes · 7 min read
How to Solve MySQL Deep Pagination: Causes and 6 Effective Optimization Strategies
Su San Talks Tech
Su San Talks Tech
Nov 26, 2023 · Databases

Why Does MySQL LIMIT offset,size Slow Down With Large Offsets?

This article explains why MySQL's LIMIT offset,size becomes slower as the offset grows, compares it with LIMIT size, details the internal execution using primary and secondary indexes, discusses deep pagination issues, and presents practical optimization techniques to improve query performance.

Deep PaginationLIMITMySQL
0 likes · 14 min read
Why Does MySQL LIMIT offset,size Slow Down With Large Offsets?
MaGe Linux Operations
MaGe Linux Operations
Aug 19, 2023 · Databases

How Deep Pagination Slowed Our System and the SQL Fixes That Saved It

This article walks through a real‑world incident where a pagination‑related slow‑query caused massive CPU spikes, details the step‑by‑step investigation, and presents several MySQL optimization techniques—including ID‑based queries, sub‑queries, and cursor‑based pagination—that ultimately resolved the performance crisis.

Database PerformanceDeep PaginationMySQL
0 likes · 10 min read
How Deep Pagination Slowed Our System and the SQL Fixes That Saved It
MaGe Linux Operations
MaGe Linux Operations
Jan 22, 2023 · Databases

How to Fix Deep Pagination Slow Queries in MySQL: A Real Incident Walkthrough

This article recounts a real‑world MySQL pagination outage, detailing how deep‑page queries caused massive CPU spikes, the step‑by‑step investigation, and the series of SQL redesigns—including ID‑based range queries, sub‑queries, scroll queries and join‑based solutions—that finally restored stable performance.

Database PerformanceDeep PaginationPagination
0 likes · 10 min read
How to Fix Deep Pagination Slow Queries in MySQL: A Real Incident Walkthrough
Architecture Digest
Architecture Digest
Sep 15, 2022 · Databases

Case Study: Resolving Deep Pagination Slow Queries in MySQL

This article walks through a real‑world incident caused by deep pagination in a MySQL‑based system, detailing the timeline of symptoms, root‑cause analysis, and a series of optimizations—including ID‑based queries, range scans, and rolling cursor techniques—that ultimately restored stable performance.

Database PerformanceDeep PaginationIndex Optimization
0 likes · 10 min read
Case Study: Resolving Deep Pagination Slow Queries in MySQL
dbaplus Community
dbaplus Community
Sep 12, 2022 · Databases

How to Resolve Deep Pagination Performance Problems in MySQL

This article walks through a real‑world incident caused by MySQL deep pagination, explains how the slow queries and CPU spikes were diagnosed, and presents several concrete optimization techniques—including ID‑based queries, range scans, sub‑queries and cursor‑based pagination—culminating in a stable production fix.

Deep PaginationMySQLSQL Optimization
0 likes · 10 min read
How to Resolve Deep Pagination Performance Problems in MySQL
Su San Talks Tech
Su San Talks Tech
Aug 13, 2022 · Databases

Why MySQL LIMIT offset slows down deep pagination and how to fix it

This article explains how MySQL processes LIMIT with an offset, why large offsets cause slower queries and deep‑pagination problems, and presents practical optimization techniques such as id‑based pagination, sub‑queries, and index‑aware strategies to improve performance.

Deep PaginationLIMITMySQL
0 likes · 14 min read
Why MySQL LIMIT offset slows down deep pagination and how to fix it
DeWu Technology
DeWu Technology
Jul 2, 2021 · Databases

MySQL Deep Pagination Optimization

MySQL deep pagination can be dramatically accelerated by ordering on the primary key, indexing the sort column, and using keyset pagination or a sub‑query join instead of scanning millions of rows, while only minor tweaks like increasing sort_buffer_size provide negligible gains.

Deep PaginationMySQLPagination
0 likes · 10 min read
MySQL Deep Pagination Optimization