Efficient Data Pagination Using LIMIT and OFFSET

SELECT
    id,
    product_name,
    price
FROM
    products
ORDER BY
    id ASC
LIMIT 10 OFFSET 20;
-- This query fetches 10 records, starting from the 21st record (skipping the first 20).
-- For page N (1-indexed) with page_size items: LIMIT page_size OFFSET (N - 1) * page_size;

How it works: This query demonstrates how to implement pagination, a fundamental technique for web applications displaying large datasets. `LIMIT 10` retrieves a maximum of 10 rows. `OFFSET 20` skips the first 20 rows. Combined, this fetches the third page of data (assuming 10 items per page). This approach is widely supported across SQL databases (e.g., PostgreSQL, MySQL, SQLite) and is critical for improving application performance and user experience by loading only necessary data.