How to Use LIMIT and OFFSET in MySQL Pagination

In MySQL database management, retrieving large datasets all at once can severely degrade application performance and user experience. This article explains how the LIMIT and OFFSET clauses work together to implement efficient database pagination, allowing developers to fetch specific, manageable subsets of data sequentially.

Understanding the LIMIT Clause

The LIMIT clause specifies the maximum number of records that a MySQL query should return. For example, if you only want to display 10 products on a single page, you would use LIMIT 10. This prevents the database from transferring unnecessary data over the network, saving both server memory and bandwidth.

Understanding the OFFSET Clause

The OFFSET clause tells the database how many rows to skip before it starts returning the data. For instance, OFFSET 20 instructs MySQL to bypass the first 20 rows of the result set and begin returning records starting from the 21st row.

Combining LIMIT and OFFSET for Pagination

By combining these two clauses, you can easily implement page-based navigation. The general formula to calculate the offset for any given page is:

OFFSET = (Page Number - 1) * Records Per Page

Here is how you would retrieve data for the first three pages of a search result, assuming you want to display 10 records per page:

• Page 1 (Records 1 to 10):

  SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 0;

• Page 2 (Records 11 to 20):

  SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 10;

• Page 3 (Records 21 to 30):

  SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

Alternative Syntax

MySQL also supports a shorthand comma-separated syntax for combining these two instructions:

LIMIT row_count OFFSET offset_value can be written as LIMIT offset_value, row_count.

For example, LIMIT 20, 10 is functionally identical to LIMIT 10 OFFSET 20. Note that in the shorthand comma syntax, the offset value is written first, which is the reverse order of the explicit LIMIT ... OFFSET syntax.

Performance Considerations

While LIMIT and OFFSET are easy to implement, they can become slow on very large tables. When you use a high offset (such as OFFSET 100000), MySQL must still scan and discard those 100,000 rows before returning the requested page. For massive datasets, developers often transition to “cursor-based pagination” (or keyset pagination) using WHERE clauses on indexed columns to maintain fast query response times.