SQL
Efficient Pagination for Large Datasets
Learn to implement efficient pagination in SQL queries using OFFSET/LIMIT or ROW_NUMBER() for better performance on large tables, crucial for web applications.
SELECT id, name, created_at
FROM products
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;
-- For very large offsets, ROW_NUMBER can be more performant (e.g., PostgreSQL, SQL Server):
SELECT id, name, created_at
FROM (
SELECT id, name, created_at, ROW_NUMBER() OVER (ORDER BY created_at DESC) as rn
FROM products
) AS subquery
WHERE rn BETWEEN 21 AND 30;
How it works: This snippet demonstrates two common SQL pagination techniques. The `LIMIT` and `OFFSET` clauses are straightforward for retrieving a specific page of results. For very large datasets and high offsets, using a subquery with the `ROW_NUMBER()` window function can be more efficient as it avoids scanning all prior rows repeatedly, particularly in databases like PostgreSQL or SQL Server. Always ensure an `ORDER BY` clause is present for consistent pagination.