Tagged articles
Keyset Pagination
12 articles · Page 1 of 1
This guide details a systematic approach to MySQL index optimization: verify slow queries using performance_schema and system metrics, prove access paths with EXPLAIN ANALYZE, create indexes with rollback strategies using ALGORITHM=INPLACE and LOCK=NONE, address deep pagination with keyset pagination and N+1 queries with batch loading, and validate improvements with latency percentiles and lock metrics.
EXPLAIN ANALYZEIndex OptimizationInnoDB
0 likes · 12 min read

The article explains why OFFSET/LIMIT pagination causes full table scans and linear performance degradation with large offsets, demonstrates a 100k-row benchmark where cursor-based pagination using primary keys runs in 0.01s versus 12.80s for OFFSET, and recommends keyset pagination for scalable systems while noting limitations for tables without unique sequential columns.
Keyset PaginationLIMITOFFSET
0 likes · 7 min read

This article analyzes eight common API performance bottlenecks in Spring Boot applications, including N+1 queries, improper entity returns, missing indexes, JOIN FETCH pagination issues, in-memory filtering, deep pagination, long transactions, and caching strategies, with code examples and optimization techniques for each.
API performanceCachingDTO Projection
0 likes · 14 min read

The article recounts how an API that originally used OFFSET pagination suffered 2‑3 seconds response time, and after rewriting the query to Keyset (Seek) pagination—adding a secondary sort key for stability and exploring cursor pagination, index‑only OFFSET, and materialized views—the same endpoint now responds in under 200 ms, with detailed benchmark comparisons.
Cursor PaginationKeyset PaginationSQL
0 likes · 7 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

This guide explores common pagination bottlenecks and presents practical solutions for single‑database and sharded environments, covering keyset pagination, bidirectional paging, join‑based paging, index design, middleware rewriting, approximate paging, and two‑phase query techniques.
Keyset PaginationSQLSharding
0 likes · 14 min read


ITPUBSep 6, 2025 · Databases
The article explains how using OFFSET for pagination can cause severe performance degradation as data grows, and demonstrates how switching to keyset (seek) pagination, optionally combined with cursor encoding, index‑only OFFSET, or materialized views, dramatically reduces query latency.
Keyset PaginationMySQLPagination
0 likes · 8 min read

This article explains the hidden performance costs of traditional OFFSET pagination in MySQL, analyzes why deep paging becomes a bottleneck, and presents keyset (cursor) pagination and other optimization techniques as efficient alternatives for handling massive or frequently changing datasets.
Database PerformanceKeyset PaginationMySQL
0 likes · 15 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 examines why MySQL pagination with large OFFSET values becomes painfully slow, explains the underlying B+‑tree index mechanics, and presents two practical solutions—keyset pagination and index‑covering subqueries—to dramatically improve query performance.
B+TreeCovering IndexKeyset Pagination
0 likes · 8 min read

A developer discovers that massive OFFSET values in MySQL pagination cause full‑table scans and severe latency, then reproduces the issue with simulated data, benchmarks the slowdown, and presents three practical solutions—including index‑covering subqueries, cursor‑based pagination, and offset limits—to restore query performance.
Index OptimizationKeyset PaginationLarge OFFSET
0 likes · 16 min read

The article explains why traditional OFFSET and LIMIT pagination becomes inefficient for large tables due to full‑table scans, demonstrates the performance gap with real‑world examples, and presents keyset (cursor‑based) pagination as a fast, index‑driven alternative with practical query patterns.
Keyset PaginationOFFSET LIMITPagination
0 likes · 6 min read
