Pagination with LIMIT & OFFSET: Building Scalable Page Feeds
Every modern web application, from e-commerce product catalogs to social media feeds and banking transaction histories, divides large datasets into bite-sized pages (e.g., 20 items per page). In SQL, basic page-based navigation is implemented using LIMIT paired with OFFSET.
1. Syntax of LIMIT with OFFSET
1-- Explicit OFFSET keyword syntax (Recommended for clarity):
2SELECT column_list
3FROM table_name
4ORDERBY sort_column
5LIMIT page_size OFFSET offset_count;
6
7-- Shorthand comma syntax (Supported by MySQL):
8-- Note: LIMIT offset, count (OFFSET COMES FIRST HERE!)
9SELECT column_list
10FROM table_name
11ORDERBY sort_column
12LIMIT offset_count, page_size;
2. The Universal Pagination Formula
To calculate the OFFSET for any given Page Number (P) and Page Size (S):
It must read all 1,000,010 rows from disk or index cache!
3
It discards the first 1,000,000 rows in memory!
4
It returns only the final 10 rows!
Result: Massive disk I/O, heavy CPU overhead, and query latency exceeding 10+ seconds!
4. The Senior Solution: Keyset (Cursor-Based) Pagination
For high-volume production tables (infinite scrolling feeds, mobile APIs), DBAs replace OFFSET with Keyset (Cursor-Based) Pagination:
1-- Instead of OFFSET, filter using the ID of the last item seen on the previous page:
2-- Page 1:
3SELECT order_id, total_amount FROM orders ORDERBY order_id ASCLIMIT10;
4-- (Suppose the last order_id on Page 1 was 1050)
5
6-- Page 2 (Instantaneous O(1) Index Lookup!):
7SELECT order_id, total_amount
8FROM orders
9WHERE order_id >1050
10ORDERBY order_id ASC
11LIMIT10;
Why Keyset Pagination is 10,000x Faster:
Because WHERE order_id > 1050 uses a direct B-Tree index seek, the database never reads or discards previous rows. Page 1,000,000 loads as quickly as Page 1!
5. Best Practices & Common Pitfalls
Deterministic Tie-Breaker: When paginating by non-unique columns (like created_at or price), always include a unique tie-breaker (like the Primary Key):
1ORDERBY created_at DESC, order_id DESC
Without a tie-breaker, rows sharing identical timestamps can swap places between page loads, causing items to appear twice or disappear!
Interactive Quiz• Knowledge Check
Multiple Choice Questions
Question 1
What is the correct formula to calculate the OFFSET value for a given page_number (1-indexed) and page_size?
Question 2
What is the difference between LIMIT 20 OFFSET 40 and the shorthand LIMIT 40, 20 in MySQL?
Question 3
Why does standard LIMIT 20 OFFSET 5000000 cause severe performance degradation on large tables?
Question 4
What high-performance pagination technique uses WHERE id > last_seen_id instead of OFFSET?
Question 5
Why must a unique column like id be added to the ORDER BY clause when paginating by a non-unique column like order_date?
Next Lesson
SQL String Functions
Continue learning with hands-on practice, examples, and exercises in the upcoming topic.