Optimizing MySQL Pagination for Large Datasets

When a MySQL table contains millions of rows, the common SELECT ... ORDER BY id DESC LIMIT offset, perpage pattern can become a serious performance bottleneck because the database must scan and discard all rows up to the offset.

Even a simple query that reads only 15 rows may still require MySQL to read thousands of rows when the offset is large, leading to increased memory usage and higher latency, as demonstrated by the 0.22 s execution time for LIMIT 100000, 15 on a 2 M‑row table.

Counting the total number of rows with SELECT COUNT(*) FROM city is fast on MyISAM but extremely slow on InnoDB (9.28 s in the example). Using SQL_CALC_FOUND_ROWS does not improve performance and can double the query time.

A more efficient way to obtain the row count on InnoDB is to force the use of the primary key index: SELECT COUNT(*) FROM city USE INDEX (PRIMARY); For fetching the actual page data, keyset pagination (also called the "seek method") avoids large offsets by remembering the last id of the current page. The next‑page query looks like:

SELECT * FROM news WHERE id < $last_id ORDER BY id DESC LIMIT $perpage;

The previous‑page query is similar but uses id > $first_id and orders ascending:

SELECT * FROM news WHERE id > $first_id ORDER BY id ASC LIMIT $perpage;

If a traditional page‑number navigation is required, a more complex query can compute the offset id for each button using user‑defined variables, allowing stable pagination even when new rows are inserted.

Another technique stores the calculated page number in an extra column and updates it in bulk, which makes retrieving any page a simple join between the data table and a pagination helper table.

For small data sets without suitable indexes, a temporary table holding all ids can be created and then paginated, though this method is the most resource‑intensive.

Overall, the key to efficient pagination is to avoid scanning large numbers of rows; using primary‑key indexes, keyset pagination, or pre‑computed page mappings dramatically reduces query time and memory consumption.