Database Management SystemsDISTINCT and LIMIT

DISTINCT and LIMIT: Controlling Result Sets

LevelBeginner

Duration60 mins

TopicDISTINCT and LIMIT

4 / 5

OFFSET: Skipping Rows

The Need to Skip Rows

While LIMIT/TOP/FETCH controls how many rows to return, OFFSET controls where to start returning rows. This combination is fundamental to pagination—displaying data in manageable chunks with 'Previous' and 'Next' navigation.

Consider an e-commerce product listing with 10,000 items. Showing all 10,000 at once is impractical: the page would load slowly, users would be overwhelmed, and bandwidth would be wasted. Instead, we show 20 products per page, letting users navigate through pages. Page 1 shows products 1-20, page 2 shows 21-40, and so on.

OFFSET enables this by specifying how many rows to skip before returning results. However, OFFSET has significant performance implications at scale that every database developer must understand.

What You Will Learn

By the end of this page, you will understand: (1) OFFSET syntax across database systems, (2) How OFFSET works internally, (3) The OFFSET performance problem at scale, (4) Why deep pagination becomes progressively slower, (5) Cross-database OFFSET syntax variations, and (6) When OFFSET is appropriate vs. when alternatives should be used.

OFFSET Syntax Across Databases

OFFSET syntax varies slightly across database systems, though the semantics are consistent: skip the first N rows, then return the rest (or a limited number).

1.1 MySQL/PostgreSQL/SQLite: LIMIT ... OFFSET

offset_mysql_postgres.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Standard syntax: LIMIT followed by OFFSET
SELECT column1, column2
FROM table_name
ORDER BY column
LIMIT row_count OFFSET skip_count;
 
-- Example: Page 3 of 20 items per page (rows 41-60)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;  -- Skip 40 rows, return next 20
 
-- Alternative MySQL syntax: LIMIT offset, count
-- (Note: offset comes FIRST in this form!)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 40, 20;  -- Skip 40 rows, return 20 rows
-- Equivalent to: LIMIT 20 OFFSET 40
 
-- PostgreSQL also accepts: OFFSET without LIMIT
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 100;  -- Skip 100 rows, return ALL remaining rows

MySQL LIMIT Syntax Trap

MySQL's LIMIT offset, count syntax puts the offset FIRST, opposite to the logical order. Many developers confuse LIMIT 40, 20 (skip 40, return 20) with LIMIT 40 (return 40). Always use the clearer LIMIT 20 OFFSET 40 syntax to avoid mistakes.

1.2 SQL Standard: OFFSET ... FETCH FIRST

offset_standard.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- SQL Standard syntax (PostgreSQL, Oracle 12c+, SQL Server 2012+)
SELECT column1, column2
FROM table_name
ORDER BY column
OFFSET skip_count ROWS
FETCH FIRST row_count ROWS ONLY;
 
-- Example: Page 3 of 20 items per page
SELECT product_name, price
FROM products
ORDER BY product_id
OFFSET 40 ROWS
FETCH FIRST 20 ROWS ONLY;
 
-- Singular ROW also valid (for readability with 1)
OFFSET 1 ROW FETCH FIRST 1 ROW ONLY;
 
-- OFFSET with FETCH NEXT (equivalent to FETCH FIRST)
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;
 
-- OFFSET alone (SQL Server requires FETCH if OFFSET used)
-- PostgreSQL is more permissive
OFFSET 100 ROWS;  -- Works in PostgreSQL, error in SQL Server

1.3 SQL Server: OFFSET with ORDER BY Requirement

offset_sqlserver.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- SQL Server requires ORDER BY when using OFFSET
SELECT product_name, price
FROM products
ORDER BY product_id       -- Required!
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY;
 
-- Error without ORDER BY:
SELECT product_name, price
FROM products
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;
-- Error: Invalid usage of OFFSET/FETCH. ORDER BY is mandatory.
 
-- SQL Server also requires FETCH with OFFSET
SELECT product_name, price
FROM products
ORDER BY product_id
OFFSET 40 ROWS;
-- Error: FETCH must be specified with OFFSET
 
-- TOP does not support OFFSET (separate mechanism)
SELECT TOP (20) product_name, price
FROM products
ORDER BY product_id
OFFSET 40;  -- Syntax error: can't combine TOP with OFFSET

OFFSET Syntax Comparison
Database	Syntax	ORDER BY Required?	Notes
MySQL	LIMIT n OFFSET m	No (but recommended)	Also: LIMIT m, n
PostgreSQL	LIMIT n OFFSET m	No (but recommended)	Also: OFFSET FETCH standard
SQLite	LIMIT n OFFSET m	No (but recommended)	LIMIT required with OFFSET
SQL Server	OFFSET m FETCH n	Yes (mandatory)	TOP cannot use OFFSET
Oracle 12c+	OFFSET m FETCH n	Recommended	ROWNUM legacy alternative
DB2	OFFSET m FETCH n	Yes	Standard compliant

How OFFSET Works Internally

Understanding OFFSET's internal mechanics reveals why it has performance limitations. The key insight: OFFSET doesn't magically jump to row N—it reads and discards rows 1 through N-1.

2.1 The Read-and-Discard Process

When executing a query with OFFSET, the database follows these steps:

Execute the base query — Apply FROM, WHERE, GROUP BY, HAVING as normal
Sort the results — Apply ORDER BY to determine row sequence
Skip OFFSET rows — Read through the first OFFSET rows, discarding each one
Return LIMIT rows — Return the next LIMIT rows (or all remaining if no LIMIT)

The critical issue is step 3: skipping rows still requires reading them.

offset_execution.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Consider this pagination query:
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 10000 ROWS
FETCH FIRST 20 ROWS ONLY;
 
-- Database execution:
-- 1. Identify all products matching criteria (full table scan if no WHERE)
-- 2. Sort ALL products by price DESC
-- 3. Read row 1, discard
-- 4. Read row 2, discard
-- 5. ... (repeat 10,000 times)
-- 6. Read row 10,001, return to client
-- 7. Read row 10,002, return to client
-- 8. ... (return 20 rows total)
-- 9. Stop at row 10,020
 
-- Result: Database did 10,020 row reads to return 20 rows!
-- The 10,000 discarded reads are wasted work.

The OFFSET Scalability Problem

OFFSET work scales linearly with the offset value. OFFSET 10 reads 10 rows. OFFSET 10,000 reads 10,000 rows. OFFSET 1,000,000 reads 1,000,000 rows. Deep pagination becomes progressively slower regardless of how few rows you request.

2.2 Visualization: Page Access Time

OFFSET Performance Degradation
Page	OFFSET Value	Rows Read	Rows Returned	Efficiency
1	0	20	20	100%
10	180	200	20	10%
100	1,980	2,000	20	1%
1,000	19,980	20,000	20	0.1%
10,000	199,980	200,000	20	0.01%
100,000	1,999,980	2,000,000	20	0.001%

2.3 Memory and I/O Impact

Beyond row reads, OFFSET causes additional overhead:

•Sorting overhead — If ORDER BY requires a sort (no supporting index), the ENTIRE result must be sorted before OFFSET can discard rows. You sort 1 million rows to return 20.
•Buffer pool pollution — Reading discarded rows brings them into memory cache, potentially evicting more useful data.
•I/O amplification — If rows span multiple disk blocks, reading 10,000 rows might touch thousands of disk blocks—even if you only return 20.
•CPU waste — Processing, deserializing, and comparing discarded rows consumes CPU cycles that produce no value.

offset_explain.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- PostgreSQL: Observe OFFSET impact with EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
ORDER BY order_date DESC
OFFSET 100000 ROWS
FETCH FIRST 20 ROWS ONLY;
 
-- Output might show:
-- Limit (actual rows=20, loops=1)
--   -> Sort (actual rows=100020, loops=1)
--     Sort Method: external merge  Disk: 45000kB  <-- Disk sort!
--     -> Seq Scan on orders (actual rows=500000, loops=1)
 
-- Note: Sort processed 100,020 rows (OFFSET + LIMIT)
-- If table has 500K rows, all were scanned for sorting
-- Disk sort indicates memory was insufficient
 
-- Compare page 1 vs page 5000 (20 items per page):
-- Page 1:    OFFSET 0     - returns in 2ms
-- Page 5000: OFFSET 99980 - returns in 850ms
-- 425x slower for the same number of returned rows!

When OFFSET is Appropriate

Despite its scaling issues, OFFSET is perfectly acceptable in many scenarios. Understanding when OFFSET is fine versus when it's problematic helps you make the right choice.

OFFSET is Appropriate When

•Small total result sets — If you're paginating 500 products, even page 25 (OFFSET 480) reads fewer than 500 rows. Perfectly fast.
•Users rarely go beyond early pages — Analytics show most users view pages 1-5. Occasional deep pagination won't hurt overall performance.
•Internal/admin tools — Admin dashboards with low traffic can tolerate slower deep pagination.
•Development and debugging — Quick ad-hoc queries for data exploration don't need optimization.
•Non-real-time processing — Batch jobs processing data can tolerate OFFSET overhead if running during low-traffic periods.
•Result set already small — If WHERE filters reduce results to hundreds, OFFSET overhead is negligible.

offset_acceptable.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Acceptable: Small filtered result set
SELECT order_id, order_date, total
FROM orders
WHERE customer_id = 12345  -- Filters to ~50 orders for this customer
ORDER BY order_date DESC
OFFSET 20 FETCH FIRST 10 ROWS ONLY;
-- With only 50 orders total, OFFSET 20 is trivial
 
-- Acceptable: Known-small table
SELECT * FROM product_categories
ORDER BY name
OFFSET 40 FETCH FIRST 20 ROWS ONLY;
-- If categories table has ~100 rows, any OFFSET is fine
 
-- Acceptable: Admin query (low traffic)
SELECT u.*, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id
ORDER BY order_count DESC
OFFSET 1000 FETCH FIRST 50 ROWS ONLY;
-- Complex query but acceptable for admin-only use

OFFSET is Problematic When

•Large result sets with deep pagination — Paginating 10 million products where users can jump to page 50,000 will be extremely slow.
•High-traffic applications — If thousands of users paginate simultaneously, OFFSET overhead multiplies.
•Real-time requirements — User-facing features with strict latency SLAs can't tolerate OFFSET jitter.
•REST APIs exposed publicly — Malicious or naive clients may request OFFSET 10000000, causing server strain.
•Mobile clients — Slow deep pagination frustrates mobile users on limited connections.
•Infinite scroll — As users scroll deeper, performance degrades noticeably.

OFFSET Edge Cases and Behaviors

OFFSET has several non-obvious behaviors that can affect query results. Understanding these edge cases prevents surprises.

4.1 OFFSET Beyond Result Set

offset_beyond.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- What happens when OFFSET exceeds available rows?
-- Table has 100 products
 
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 150 FETCH FIRST 20 ROWS ONLY;
 
-- Result: Empty result set (0 rows)
-- No error, no padding - just empty
-- This is standard behavior across all databases
 
-- Check if you've gone past the end:
-- If result count < requested limit, you might be at/past the end
-- But OFFSET 95 FETCH 20 returns 5 rows (rows 96-100)

4.2 OFFSET 0 (First Page)

offset_zero.sql
1
2
3
4
5
6
7
8
9
10
-- OFFSET 0 is equivalent to no OFFSET
SELECT * FROM products ORDER BY price LIMIT 20 OFFSET 0;
SELECT * FROM products ORDER BY price LIMIT 20;
-- Both return the same first 20 rows
 
-- Some applications always include OFFSET for consistency:
-- Page calculation: OFFSET = (page_number - 1) * page_size
-- Page 1: OFFSET = (1-1) * 20 = 0
-- Page 2: OFFSET = (2-1) * 20 = 20
-- Page 3: OFFSET = (3-1) * 20 = 40

4.3 Negative OFFSET

offset_negative.sql
1
2
3
4
5
6
7
-- Negative OFFSET values are errors in all databases
SELECT * FROM products ORDER BY price LIMIT 20 OFFSET -10;
-- Error: OFFSET must be a non-negative integer
 
-- Applications should validate page numbers:
-- If page_number <= 0: treat as page 1
-- If page_number > max_page: return empty or last page

4.4 Non-Deterministic Results Without Proper ORDER BY

offset_deterministic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- DANGEROUS: OFFSET without ORDER BY
SELECT * FROM products LIMIT 20 OFFSET 40;
-- Which rows are skipped? Unknown and unstable!
-- May return different results on repeated execution
 
-- DANGEROUS: ORDER BY column with duplicates
SELECT * FROM products ORDER BY category LIMIT 20 OFFSET 40;
-- If multiple products have same category, their relative order
-- within that category is undefined. Products might appear on
-- multiple pages or skip pages as data changes.
 
-- SAFE: Add unique tiebreaker to ORDER BY
SELECT * FROM products 
ORDER BY category, product_id  -- product_id breaks ties
LIMIT 20 OFFSET 40;
-- Now every product has a unique position in the ordering

The Pagination Stability Problem

If you ORDER BY a non-unique column, rows with the same value have arbitrary positions. Between page loads, data changes or parallel queries may shift these rows, causing duplicates or gaps in pagination. Always include a unique column (like primary key) in ORDER BY for stable pagination.

4.5 Concurrent Modification Issues

offset_concurrent.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Scenario: User views page 1, then page 2
-- Time T1: User requests page 1
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- Returns newest 20 products (IDs 100-81)
 
-- Time T2: Someone adds a new product (ID 101)
 
-- Time T3: User requests page 2
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 20;
-- Now returns products 80-61 (shifted by new product)
-- Product ID 81 appeared on page 1 AND page 2!
 
-- Scenario: Deletion causes gap
-- Time T1: User views page 1 (products 100-81)
-- Time T2: Product 95 is deleted
-- Time T3: User views page 2
-- Products 80-61 returned, but product 94 was never seen!
 
-- These issues are inherent to OFFSET pagination with live data
-- Cursor-based pagination (next page) handles this better

When to Consider OFFSET Alternatives

Given OFFSET's limitations, several alternative pagination approaches exist. This section introduces when to consider them; the next page covers implementation details.

5.1 Keyset/Cursor Pagination

Instead of 'skip N rows,' keyset pagination says 'get rows after this value.' It uses indexed columns to jump directly to the starting point.

keyset_intro.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- OFFSET approach (slow for deep pages)
SELECT * FROM products
ORDER BY product_id
OFFSET 10000 LIMIT 20;
-- Must read 10,000 rows to skip them
 
-- Keyset approach (fast for any page)
-- Client remembers last seen product_id (e.g., 10523)
SELECT * FROM products
WHERE product_id > 10523
ORDER BY product_id
LIMIT 20;
-- Index seek to 10524, read only 20 rows!
-- Same speed whether it's "page 2" or "page 5000"

When to Use Keyset Instead of OFFSET

•Infinite scroll interfaces (progressively loading more data)
•APIs with large result sets (millions of rows)
•Real-time feeds where data changes frequently
•Mobile applications requiring consistent performance
•Any scenario where users may access deep pages

5.2 Seek Method

The seek method is a generalization of keyset pagination that handles composite ordering and descending sorts:

seek_intro.sql
1
2
3
4
5
6
7
8
9
10
-- Paginating by multiple columns with DESC order
-- Last seen: created_at='2024-01-15 10:30:00', id=5000
 
SELECT * FROM events
WHERE (created_at, id) < ('2024-01-15 10:30:00', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 20;
 
-- Row comparison uses tuple ordering
-- Efficiently uses index on (created_at DESC, id DESC)

5.3 Deferred Joins

For queries requiring expensive column fetches, fetch only keys first, then join:

deferred_join.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Slow: OFFSET reads and discards full rows
SELECT * FROM products
ORDER BY created_at DESC
OFFSET 10000 LIMIT 20;
-- Reads 10,020 full rows (including large description TEXT)
 
-- Fast: OFFSET on minimal columns, then fetch full rows
SELECT p.* FROM products p
INNER JOIN (
    SELECT product_id FROM products
    ORDER BY created_at DESC
    OFFSET 10000 LIMIT 20
) keys ON p.product_id = keys.product_id
ORDER BY p.created_at DESC;
-- Inner query offsets only (id, created_at) - much smaller
-- Outer query fetches only 20 full rows

Hybrid Approaches

Real applications often combine approaches: OFFSET for small result sets or early pages, keyset for deep pagination or infinite scroll. The next page on Pagination covers practical implementation patterns combining these techniques.

OFFSET Best Practices

When you do use OFFSET, follow these practices to minimize issues and prepare for future optimization.

OFFSET Best Practices

•Always include ORDER BY — Without ORDER BY, OFFSET behavior is undefined. Always specify explicit ordering for predictable pagination.
•Make ORDER BY deterministic — Include a unique column (usually primary key) as the final ORDER BY criterion to ensure stable row positions.
•Index the ORDER BY columns — Proper indexes enable efficient sorted access, significantly improving OFFSET performance.
•Limit maximum OFFSET — Cap how deep users can paginate. If beyond page 50 (OFFSET 1000), require search/filters instead.
•Return total count separately — Don't compute total count on every page request. Cache it, compute it asynchronously, or skip it (show '10,000+' instead of exact count).
•Consider result set size BEFORE offset — Add WHERE filters to reduce the base result set; smaller sets make OFFSET faster.
•Monitor performance degradation — Track pagination query times across page depths. Alert if deep pages become too slow.
•Prepare for migration — Structure pagination APIs to support switching to keyset later (return 'next cursor' even if not used).

offset_best_practices.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Best Practice: Deterministic ORDER BY with index
CREATE INDEX idx_products_created 
ON products(created_at DESC, product_id DESC);
 
SELECT * FROM products
ORDER BY created_at DESC, product_id DESC  -- Deterministic
OFFSET 40 LIMIT 20;
 
-- Best Practice: Limit maximum offset in application
-- Application code pseudo:
-- max_offset = 1000  // ~50 pages of 20
-- if requested_offset > max_offset:
--     return error("Use search for results beyond page 50")
 
-- Best Practice: Compute count separately (or skip it)
-- Instead of:
SELECT COUNT(*) as total, p.*
FROM products p
ORDER BY created_at DESC
OFFSET 40 LIMIT 20;  -- COUNT(*) scans all rows!
 
-- Do:
SELECT * FROM products
ORDER BY created_at DESC, product_id DESC
OFFSET 40 LIMIT 21;  -- Request 21 to detect "has more"
-- has_more = (rows_returned == 21)
-- Return first 20 rows only
 
-- Or cache count separately:
-- total_products = cache.get('product_count')  // Updated periodically

OFFSET with Complex Queries

OFFSET interacts with other query features in specific ways. Understanding these interactions prevents unexpected results.

7.1 OFFSET with DISTINCT

offset_distinct.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- DISTINCT is applied before OFFSET
SELECT DISTINCT category
FROM products
ORDER BY category
OFFSET 5 LIMIT 10;
 
-- Execution:
-- 1. Select all categories (with duplicates)
-- 2. Eliminate duplicates (DISTINCT)
-- 3. Sort unique categories
-- 4. Skip 5
-- 5. Return next 10
 
-- If 50 unique categories exist, returns categories 6-15

7.2 OFFSET with GROUP BY

offset_group_by.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- OFFSET applies to grouped results
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC
OFFSET 5 LIMIT 10;
 
-- Execution:
-- 1. Group all employees by department
-- 2. Count each group
-- 3. Sort by count descending
-- 4. Skip 5 departments
-- 5. Return next 10 departments
 
-- Paginates the aggregated results, not individual rows

7.3 OFFSET with UNION

offset_union.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- OFFSET applies to the entire UNION result
(SELECT product_name, 'on_sale' as type FROM products WHERE on_sale = true)
UNION ALL
(SELECT product_name, 'new' as type FROM products WHERE is_new = true)
ORDER BY product_name
OFFSET 20 LIMIT 20;
 
-- The combined result is sorted and paginated as one set
 
-- To paginate each part separately, use subqueries:
SELECT * FROM (
    SELECT product_name FROM products WHERE on_sale = true
    ORDER BY product_name LIMIT 10 OFFSET 10
) sale_products
UNION ALL
SELECT * FROM (
    SELECT product_name FROM products WHERE is_new = true
    ORDER BY product_name LIMIT 10 OFFSET 10
) new_products;

7.4 OFFSET with Subqueries

offset_subquery.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- OFFSET in outer query paginates subquery results
SELECT *, RANK() OVER (ORDER BY price DESC) as price_rank
FROM (
    SELECT * FROM products WHERE category = 'Electronics'
) electronics
ORDER BY price DESC
OFFSET 10 LIMIT 10;
 
-- Subquery filters to electronics
-- Window function adds ranking BEFORE offset
-- OFFSET skips first 10 ranked products
-- Returns products ranked 11-20
 
-- OFFSET in correlated subquery (rare, usually inefficient)
SELECT p.product_name,
       (SELECT COUNT(*) FROM reviews r 
        WHERE r.product_id = p.product_id 
        LIMIT 1 OFFSET 0) as review_count  -- Pointless OFFSET 0
FROM products p;

Summary: Understanding OFFSET

OFFSET is a fundamental but misunderstood SQL feature. Let's consolidate the key insights:

Key Takeaways

•OFFSET skips rows by reading them — The database reads and discards rows 1 through OFFSET. There's no magic 'jump to position N.'
•Performance degrades linearly with OFFSET — OFFSET 10 is 10x faster than OFFSET 100. Deep pagination becomes slow regardless of LIMIT size.
•Syntax varies across databases — MySQL uses LIMIT n OFFSET m, SQL Server uses OFFSET m FETCH NEXT n, and ordering requirements differ.
•Always use ORDER BY — Without ORDER BY, OFFSET results are non-deterministic and may vary between executions.
•Include a unique tiebreaker — If ORDER BY columns have duplicates, rows may appear on multiple pages or skip pages. Add primary key to ORDER BY.
•Concurrent changes cause inconsistency — Inserts/deletes between page requests can cause rows to appear twice or be skipped entirely.
•OFFSET is fine for small datasets — Paginating hundreds of rows is fast even with OFFSET. Problems emerge with large datasets and deep pagination.
•Alternatives exist for scalability — Keyset/cursor pagination provides consistent O(1) performance regardless of page depth.

What's Next:

With OFFSET understood, we're ready to integrate everything into practical pagination implementation. The next page covers pagination patterns—calculation formulas, UI considerations, keyset implementation, and strategies for building scalable paginated interfaces.

Page Complete

You now understand OFFSET deeply—its mechanics, performance characteristics, edge cases, and limitations. You can make informed decisions about when OFFSET is appropriate and when to consider alternatives for scalable pagination.

4 / 5

Loading learning content...

Database Management SystemsDISTINCT and LIMIT

DISTINCT and LIMIT: Controlling Result Sets

LevelBeginner

Duration60 mins

TopicDISTINCT and LIMIT

4 / 5

OFFSET: Skipping Rows

The Need to Skip Rows

OFFSET enables this by specifying how many rows to skip before returning results. However, OFFSET has significant performance implications at scale that every database developer must understand.

What You Will Learn

OFFSET Syntax Across Databases

OFFSET syntax varies slightly across database systems, though the semantics are consistent: skip the first N rows, then return the rest (or a limited number).

1.1 MySQL/PostgreSQL/SQLite: LIMIT ... OFFSET

offset_mysql_postgres.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- Standard syntax: LIMIT followed by OFFSET
SELECT column1, column2
FROM table_name
ORDER BY column
LIMIT row_count OFFSET skip_count;
 
-- Example: Page 3 of 20 items per page (rows 41-60)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 20 OFFSET 40;  -- Skip 40 rows, return next 20
 
-- Alternative MySQL syntax: LIMIT offset, count
-- (Note: offset comes FIRST in this form!)
SELECT product_name, price
FROM products
ORDER BY product_id
LIMIT 40, 20;  -- Skip 40 rows, return 20 rows
-- Equivalent to: LIMIT 20 OFFSET 40
 
-- PostgreSQL also accepts: OFFSET without LIMIT
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 100;  -- Skip 100 rows, return ALL remaining rows

MySQL LIMIT Syntax Trap

1.2 SQL Standard: OFFSET ... FETCH FIRST

offset_standard.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- SQL Standard syntax (PostgreSQL, Oracle 12c+, SQL Server 2012+)
SELECT column1, column2
FROM table_name
ORDER BY column
OFFSET skip_count ROWS
FETCH FIRST row_count ROWS ONLY;
 
-- Example: Page 3 of 20 items per page
SELECT product_name, price
FROM products
ORDER BY product_id
OFFSET 40 ROWS
FETCH FIRST 20 ROWS ONLY;
 
-- Singular ROW also valid (for readability with 1)
OFFSET 1 ROW FETCH FIRST 1 ROW ONLY;
 
-- OFFSET with FETCH NEXT (equivalent to FETCH FIRST)
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;
 
-- OFFSET alone (SQL Server requires FETCH if OFFSET used)
-- PostgreSQL is more permissive
OFFSET 100 ROWS;  -- Works in PostgreSQL, error in SQL Server

1.3 SQL Server: OFFSET with ORDER BY Requirement

offset_sqlserver.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
-- SQL Server requires ORDER BY when using OFFSET
SELECT product_name, price
FROM products
ORDER BY product_id       -- Required!
OFFSET 40 ROWS
FETCH NEXT 20 ROWS ONLY;
 
-- Error without ORDER BY:
SELECT product_name, price
FROM products
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;
-- Error: Invalid usage of OFFSET/FETCH. ORDER BY is mandatory.
 
-- SQL Server also requires FETCH with OFFSET
SELECT product_name, price
FROM products
ORDER BY product_id
OFFSET 40 ROWS;
-- Error: FETCH must be specified with OFFSET
 
-- TOP does not support OFFSET (separate mechanism)
SELECT TOP (20) product_name, price
FROM products
ORDER BY product_id
OFFSET 40;  -- Syntax error: can't combine TOP with OFFSET

OFFSET Syntax Comparison
Database	Syntax	ORDER BY Required?	Notes
MySQL	LIMIT n OFFSET m	No (but recommended)	Also: LIMIT m, n
PostgreSQL	LIMIT n OFFSET m	No (but recommended)	Also: OFFSET FETCH standard
SQLite	LIMIT n OFFSET m	No (but recommended)	LIMIT required with OFFSET
SQL Server	OFFSET m FETCH n	Yes (mandatory)	TOP cannot use OFFSET
Oracle 12c+	OFFSET m FETCH n	Recommended	ROWNUM legacy alternative
DB2	OFFSET m FETCH n	Yes	Standard compliant

How OFFSET Works Internally

Understanding OFFSET's internal mechanics reveals why it has performance limitations. The key insight: OFFSET doesn't magically jump to row N—it reads and discards rows 1 through N-1.

2.1 The Read-and-Discard Process

When executing a query with OFFSET, the database follows these steps:

Execute the base query — Apply FROM, WHERE, GROUP BY, HAVING as normal
Sort the results — Apply ORDER BY to determine row sequence
Skip OFFSET rows — Read through the first OFFSET rows, discarding each one
Return LIMIT rows — Return the next LIMIT rows (or all remaining if no LIMIT)

The critical issue is step 3: skipping rows still requires reading them.

offset_execution.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Consider this pagination query:
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 10000 ROWS
FETCH FIRST 20 ROWS ONLY;
 
-- Database execution:
-- 1. Identify all products matching criteria (full table scan if no WHERE)
-- 2. Sort ALL products by price DESC
-- 3. Read row 1, discard
-- 4. Read row 2, discard
-- 5. ... (repeat 10,000 times)
-- 6. Read row 10,001, return to client
-- 7. Read row 10,002, return to client
-- 8. ... (return 20 rows total)
-- 9. Stop at row 10,020
 
-- Result: Database did 10,020 row reads to return 20 rows!
-- The 10,000 discarded reads are wasted work.

The OFFSET Scalability Problem

2.2 Visualization: Page Access Time

OFFSET Performance Degradation
Page	OFFSET Value	Rows Read	Rows Returned	Efficiency
1	0	20	20	100%
10	180	200	20	10%
100	1,980	2,000	20	1%
1,000	19,980	20,000	20	0.1%
10,000	199,980	200,000	20	0.01%
100,000	1,999,980	2,000,000	20	0.001%

2.3 Memory and I/O Impact

Beyond row reads, OFFSET causes additional overhead:

•Sorting overhead — If ORDER BY requires a sort (no supporting index), the ENTIRE result must be sorted before OFFSET can discard rows. You sort 1 million rows to return 20.
•Buffer pool pollution — Reading discarded rows brings them into memory cache, potentially evicting more useful data.
•I/O amplification — If rows span multiple disk blocks, reading 10,000 rows might touch thousands of disk blocks—even if you only return 20.
•CPU waste — Processing, deserializing, and comparing discarded rows consumes CPU cycles that produce no value.

offset_explain.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- PostgreSQL: Observe OFFSET impact with EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
ORDER BY order_date DESC
OFFSET 100000 ROWS
FETCH FIRST 20 ROWS ONLY;
 
-- Output might show:
-- Limit (actual rows=20, loops=1)
--   -> Sort (actual rows=100020, loops=1)
--     Sort Method: external merge  Disk: 45000kB  <-- Disk sort!
--     -> Seq Scan on orders (actual rows=500000, loops=1)
 
-- Note: Sort processed 100,020 rows (OFFSET + LIMIT)
-- If table has 500K rows, all were scanned for sorting
-- Disk sort indicates memory was insufficient
 
-- Compare page 1 vs page 5000 (20 items per page):
-- Page 1:    OFFSET 0     - returns in 2ms
-- Page 5000: OFFSET 99980 - returns in 850ms
-- 425x slower for the same number of returned rows!

When OFFSET is Appropriate

Despite its scaling issues, OFFSET is perfectly acceptable in many scenarios. Understanding when OFFSET is fine versus when it's problematic helps you make the right choice.

OFFSET is Appropriate When

•Small total result sets — If you're paginating 500 products, even page 25 (OFFSET 480) reads fewer than 500 rows. Perfectly fast.
•Users rarely go beyond early pages — Analytics show most users view pages 1-5. Occasional deep pagination won't hurt overall performance.
•Internal/admin tools — Admin dashboards with low traffic can tolerate slower deep pagination.
•Development and debugging — Quick ad-hoc queries for data exploration don't need optimization.
•Non-real-time processing — Batch jobs processing data can tolerate OFFSET overhead if running during low-traffic periods.
•Result set already small — If WHERE filters reduce results to hundreds, OFFSET overhead is negligible.

offset_acceptable.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Acceptable: Small filtered result set
SELECT order_id, order_date, total
FROM orders
WHERE customer_id = 12345  -- Filters to ~50 orders for this customer
ORDER BY order_date DESC
OFFSET 20 FETCH FIRST 10 ROWS ONLY;
-- With only 50 orders total, OFFSET 20 is trivial
 
-- Acceptable: Known-small table
SELECT * FROM product_categories
ORDER BY name
OFFSET 40 FETCH FIRST 20 ROWS ONLY;
-- If categories table has ~100 rows, any OFFSET is fine
 
-- Acceptable: Admin query (low traffic)
SELECT u.*, COUNT(o.order_id) as order_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id
ORDER BY order_count DESC
OFFSET 1000 FETCH FIRST 50 ROWS ONLY;
-- Complex query but acceptable for admin-only use

OFFSET is Problematic When

•Large result sets with deep pagination — Paginating 10 million products where users can jump to page 50,000 will be extremely slow.
•High-traffic applications — If thousands of users paginate simultaneously, OFFSET overhead multiplies.
•Real-time requirements — User-facing features with strict latency SLAs can't tolerate OFFSET jitter.
•REST APIs exposed publicly — Malicious or naive clients may request OFFSET 10000000, causing server strain.
•Mobile clients — Slow deep pagination frustrates mobile users on limited connections.
•Infinite scroll — As users scroll deeper, performance degrades noticeably.

OFFSET Edge Cases and Behaviors

OFFSET has several non-obvious behaviors that can affect query results. Understanding these edge cases prevents surprises.

4.1 OFFSET Beyond Result Set

offset_beyond.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- What happens when OFFSET exceeds available rows?
-- Table has 100 products
 
SELECT product_name, price
FROM products
ORDER BY price DESC
OFFSET 150 FETCH FIRST 20 ROWS ONLY;
 
-- Result: Empty result set (0 rows)
-- No error, no padding - just empty
-- This is standard behavior across all databases
 
-- Check if you've gone past the end:
-- If result count < requested limit, you might be at/past the end
-- But OFFSET 95 FETCH 20 returns 5 rows (rows 96-100)

4.2 OFFSET 0 (First Page)

offset_zero.sql
1
2
3
4
5
6
7
8
9
10
-- OFFSET 0 is equivalent to no OFFSET
SELECT * FROM products ORDER BY price LIMIT 20 OFFSET 0;
SELECT * FROM products ORDER BY price LIMIT 20;
-- Both return the same first 20 rows
 
-- Some applications always include OFFSET for consistency:
-- Page calculation: OFFSET = (page_number - 1) * page_size
-- Page 1: OFFSET = (1-1) * 20 = 0
-- Page 2: OFFSET = (2-1) * 20 = 20
-- Page 3: OFFSET = (3-1) * 20 = 40

4.3 Negative OFFSET

offset_negative.sql
1
2
3
4
5
6
7
-- Negative OFFSET values are errors in all databases
SELECT * FROM products ORDER BY price LIMIT 20 OFFSET -10;
-- Error: OFFSET must be a non-negative integer
 
-- Applications should validate page numbers:
-- If page_number <= 0: treat as page 1
-- If page_number > max_page: return empty or last page

4.4 Non-Deterministic Results Without Proper ORDER BY

offset_deterministic.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- DANGEROUS: OFFSET without ORDER BY
SELECT * FROM products LIMIT 20 OFFSET 40;
-- Which rows are skipped? Unknown and unstable!
-- May return different results on repeated execution
 
-- DANGEROUS: ORDER BY column with duplicates
SELECT * FROM products ORDER BY category LIMIT 20 OFFSET 40;
-- If multiple products have same category, their relative order
-- within that category is undefined. Products might appear on
-- multiple pages or skip pages as data changes.
 
-- SAFE: Add unique tiebreaker to ORDER BY
SELECT * FROM products 
ORDER BY category, product_id  -- product_id breaks ties
LIMIT 20 OFFSET 40;
-- Now every product has a unique position in the ordering

The Pagination Stability Problem

4.5 Concurrent Modification Issues

offset_concurrent.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Scenario: User views page 1, then page 2
-- Time T1: User requests page 1
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 0;
-- Returns newest 20 products (IDs 100-81)
 
-- Time T2: Someone adds a new product (ID 101)
 
-- Time T3: User requests page 2
SELECT * FROM products ORDER BY created_at DESC LIMIT 20 OFFSET 20;
-- Now returns products 80-61 (shifted by new product)
-- Product ID 81 appeared on page 1 AND page 2!
 
-- Scenario: Deletion causes gap
-- Time T1: User views page 1 (products 100-81)
-- Time T2: Product 95 is deleted
-- Time T3: User views page 2
-- Products 80-61 returned, but product 94 was never seen!
 
-- These issues are inherent to OFFSET pagination with live data
-- Cursor-based pagination (next page) handles this better

When to Consider OFFSET Alternatives

Given OFFSET's limitations, several alternative pagination approaches exist. This section introduces when to consider them; the next page covers implementation details.

5.1 Keyset/Cursor Pagination

Instead of 'skip N rows,' keyset pagination says 'get rows after this value.' It uses indexed columns to jump directly to the starting point.

keyset_intro.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- OFFSET approach (slow for deep pages)
SELECT * FROM products
ORDER BY product_id
OFFSET 10000 LIMIT 20;
-- Must read 10,000 rows to skip them
 
-- Keyset approach (fast for any page)
-- Client remembers last seen product_id (e.g., 10523)
SELECT * FROM products
WHERE product_id > 10523
ORDER BY product_id
LIMIT 20;
-- Index seek to 10524, read only 20 rows!
-- Same speed whether it's "page 2" or "page 5000"

When to Use Keyset Instead of OFFSET

•Infinite scroll interfaces (progressively loading more data)
•APIs with large result sets (millions of rows)
•Real-time feeds where data changes frequently
•Mobile applications requiring consistent performance
•Any scenario where users may access deep pages

5.2 Seek Method

The seek method is a generalization of keyset pagination that handles composite ordering and descending sorts:

seek_intro.sql
1
2
3
4
5
6
7
8
9
10
-- Paginating by multiple columns with DESC order
-- Last seen: created_at='2024-01-15 10:30:00', id=5000
 
SELECT * FROM events
WHERE (created_at, id) < ('2024-01-15 10:30:00', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 20;
 
-- Row comparison uses tuple ordering
-- Efficiently uses index on (created_at DESC, id DESC)

5.3 Deferred Joins

For queries requiring expensive column fetches, fetch only keys first, then join:

deferred_join.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Slow: OFFSET reads and discards full rows
SELECT * FROM products
ORDER BY created_at DESC
OFFSET 10000 LIMIT 20;
-- Reads 10,020 full rows (including large description TEXT)
 
-- Fast: OFFSET on minimal columns, then fetch full rows
SELECT p.* FROM products p
INNER JOIN (
    SELECT product_id FROM products
    ORDER BY created_at DESC
    OFFSET 10000 LIMIT 20
) keys ON p.product_id = keys.product_id
ORDER BY p.created_at DESC;
-- Inner query offsets only (id, created_at) - much smaller
-- Outer query fetches only 20 full rows

Hybrid Approaches

OFFSET Best Practices

When you do use OFFSET, follow these practices to minimize issues and prepare for future optimization.

OFFSET Best Practices

•Always include ORDER BY — Without ORDER BY, OFFSET behavior is undefined. Always specify explicit ordering for predictable pagination.
•Make ORDER BY deterministic — Include a unique column (usually primary key) as the final ORDER BY criterion to ensure stable row positions.
•Index the ORDER BY columns — Proper indexes enable efficient sorted access, significantly improving OFFSET performance.
•Limit maximum OFFSET — Cap how deep users can paginate. If beyond page 50 (OFFSET 1000), require search/filters instead.
•Return total count separately — Don't compute total count on every page request. Cache it, compute it asynchronously, or skip it (show '10,000+' instead of exact count).
•Consider result set size BEFORE offset — Add WHERE filters to reduce the base result set; smaller sets make OFFSET faster.
•Monitor performance degradation — Track pagination query times across page depths. Alert if deep pages become too slow.
•Prepare for migration — Structure pagination APIs to support switching to keyset later (return 'next cursor' even if not used).

offset_best_practices.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Best Practice: Deterministic ORDER BY with index
CREATE INDEX idx_products_created 
ON products(created_at DESC, product_id DESC);
 
SELECT * FROM products
ORDER BY created_at DESC, product_id DESC  -- Deterministic
OFFSET 40 LIMIT 20;
 
-- Best Practice: Limit maximum offset in application
-- Application code pseudo:
-- max_offset = 1000  // ~50 pages of 20
-- if requested_offset > max_offset:
--     return error("Use search for results beyond page 50")
 
-- Best Practice: Compute count separately (or skip it)
-- Instead of:
SELECT COUNT(*) as total, p.*
FROM products p
ORDER BY created_at DESC
OFFSET 40 LIMIT 20;  -- COUNT(*) scans all rows!
 
-- Do:
SELECT * FROM products
ORDER BY created_at DESC, product_id DESC
OFFSET 40 LIMIT 21;  -- Request 21 to detect "has more"
-- has_more = (rows_returned == 21)
-- Return first 20 rows only
 
-- Or cache count separately:
-- total_products = cache.get('product_count')  // Updated periodically

OFFSET with Complex Queries

OFFSET interacts with other query features in specific ways. Understanding these interactions prevents unexpected results.

7.1 OFFSET with DISTINCT

offset_distinct.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- DISTINCT is applied before OFFSET
SELECT DISTINCT category
FROM products
ORDER BY category
OFFSET 5 LIMIT 10;
 
-- Execution:
-- 1. Select all categories (with duplicates)
-- 2. Eliminate duplicates (DISTINCT)
-- 3. Sort unique categories
-- 4. Skip 5
-- 5. Return next 10
 
-- If 50 unique categories exist, returns categories 6-15

7.2 OFFSET with GROUP BY

offset_group_by.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- OFFSET applies to grouped results
SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
ORDER BY employee_count DESC
OFFSET 5 LIMIT 10;
 
-- Execution:
-- 1. Group all employees by department
-- 2. Count each group
-- 3. Sort by count descending
-- 4. Skip 5 departments
-- 5. Return next 10 departments
 
-- Paginates the aggregated results, not individual rows

7.3 OFFSET with UNION

offset_union.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- OFFSET applies to the entire UNION result
(SELECT product_name, 'on_sale' as type FROM products WHERE on_sale = true)
UNION ALL
(SELECT product_name, 'new' as type FROM products WHERE is_new = true)
ORDER BY product_name
OFFSET 20 LIMIT 20;
 
-- The combined result is sorted and paginated as one set
 
-- To paginate each part separately, use subqueries:
SELECT * FROM (
    SELECT product_name FROM products WHERE on_sale = true
    ORDER BY product_name LIMIT 10 OFFSET 10
) sale_products
UNION ALL
SELECT * FROM (
    SELECT product_name FROM products WHERE is_new = true
    ORDER BY product_name LIMIT 10 OFFSET 10
) new_products;

7.4 OFFSET with Subqueries

offset_subquery.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- OFFSET in outer query paginates subquery results
SELECT *, RANK() OVER (ORDER BY price DESC) as price_rank
FROM (
    SELECT * FROM products WHERE category = 'Electronics'
) electronics
ORDER BY price DESC
OFFSET 10 LIMIT 10;
 
-- Subquery filters to electronics
-- Window function adds ranking BEFORE offset
-- OFFSET skips first 10 ranked products
-- Returns products ranked 11-20
 
-- OFFSET in correlated subquery (rare, usually inefficient)
SELECT p.product_name,
       (SELECT COUNT(*) FROM reviews r 
        WHERE r.product_id = p.product_id 
        LIMIT 1 OFFSET 0) as review_count  -- Pointless OFFSET 0
FROM products p;

Summary: Understanding OFFSET

OFFSET is a fundamental but misunderstood SQL feature. Let's consolidate the key insights:

Key Takeaways

•OFFSET skips rows by reading them — The database reads and discards rows 1 through OFFSET. There's no magic 'jump to position N.'
•Performance degrades linearly with OFFSET — OFFSET 10 is 10x faster than OFFSET 100. Deep pagination becomes slow regardless of LIMIT size.
•Syntax varies across databases — MySQL uses LIMIT n OFFSET m, SQL Server uses OFFSET m FETCH NEXT n, and ordering requirements differ.
•Always use ORDER BY — Without ORDER BY, OFFSET results are non-deterministic and may vary between executions.
•Include a unique tiebreaker — If ORDER BY columns have duplicates, rows may appear on multiple pages or skip pages. Add primary key to ORDER BY.
•Concurrent changes cause inconsistency — Inserts/deletes between page requests can cause rows to appear twice or be skipped entirely.
•OFFSET is fine for small datasets — Paginating hundreds of rows is fast even with OFFSET. Problems emerge with large datasets and deep pagination.
•Alternatives exist for scalability — Keyset/cursor pagination provides consistent O(1) performance regardless of page depth.

What's Next:

Page Complete

4 / 5