Tagged articles
Deep Pagination
23 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 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

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 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


ITPUBJul 17, 2026 · Backend Development
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

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

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

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

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

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

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

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

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

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

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

This article walks through a real‑world MySQL deep‑pagination incident, detailing the timeline, root‑cause analysis, and multiple optimization techniques—including ID‑based queries, sub‑queries, and cursor pagination—that ultimately stabilized the system and eliminated slow‑SQL overload.
Deep PaginationMySQLPagination
0 likes · 10 min read

This guide explores practical strategies for managing Elasticsearch indexes, covering management approaches, structural maintenance, data synchronization, refresh policies, and deep pagination techniques to avoid common pitfalls and ensure reliable search performance.
BackendDeep PaginationElasticsearch
0 likes · 9 min read

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

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

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

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

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

This article explains why deep pagination in Elasticsearch leads to high CPU usage, introduces core concepts such as indices, shards, nodes, and documents, and details the distributed CRUD operations, query and fetch phases, and segment management that affect search performance.
Deep PaginationElasticsearchSharding
0 likes · 12 min read
