The World of MySQL & MariaDB

Master MySQL LIMIT & OFFSET: Fast Pagination & Performance

Operators and Clauses
目次

1. Basic Usage of LIMIT and OFFSET

In MySQL, LIMIT and OFFSET are very useful features for efficiently retrieving a specific range of data from the database. By understanding them, you can efficiently fetch only the data you need and improve application performance. First, we will explain their basic usage in detail.

Roles of the LIMIT clause and OFFSET clause

LIMIT clause restricts the number of rows to retrieve. In contrast, the OFFSET clause specifies the starting position for data retrieval. By combining these two, you can extract only the required range of data from a large dataset.

Example

The following example retrieves 10 rows of data starting from the 20th row.
SELECT * FROM table_name LIMIT 10 OFFSET 20;
In this query, OFFSET causes retrieval to start from the 20th row, and LIMIT fetches 10 rows of data. Also, if OFFSET is omitted, it is assumed to be 0, so data is retrieved from the beginning for the specified number of rows.

Behavior when the OFFSET clause is omitted

If OFFSET is omitted, data is retrieved from the start of the table for the number of rows specified by LIMIT. For example, the following query retrieves the first 10 rows of the table.
SELECT * FROM table_name LIMIT 10;

2. How to Implement Pagination

When handling large amounts of data on websites or applications, displaying all data at once is not realistic. Therefore, implementing pagination, which splits data and displays it page by page, is common. By leveraging LIMIT and OFFSET, you can retrieve different data for each page.

Setting LIMIT and OFFSET According to Page Number

If you set the number of items displayed per page to 10, the first page has an OFFSET of 0, the second page an OFFSET of 10, the third page 20, and so on, allowing you to configure LIMIT and OFFSET based on the page number.

Example

Query for the first page:
SELECT * FROM table_name LIMIT 10 OFFSET 0;
Query for the second page:
SELECT * FROM table_name LIMIT 10 OFFSET 10;
In this way, by varying OFFSET according to the page number, you can correctly retrieve data for different pages.

Things to Keep in Mind for Pagination

When implementing pagination with OFFSET, be aware that the deeper into the result set you go, the longer the query processing time becomes. This is because the database reads all rows up to the OFFSET.

3. Performance considerations when using OFFSET

When the data volume is massive, overusing OFFSET can significantly degrade query performance. For example, retrieving the latter part of a table that spans millions of rows requires the database to scan all rows until it reaches the specified OFFSET position.

Example of performance degradation

If you retrieve 10 rows starting from the 1,000,000th row, the database must skip the first 999,990 rows. Consequently, as the OFFSET value grows, the processing time also increases.

Improving performance by leveraging indexes

When using LIMIT or OFFSET, setting appropriate indexes can reduce query execution time. In particular, indexing the search criteria helps the database locate the target data more efficiently.

4. Seek Method for Efficient Pagination

When dealing with large amounts of data, the “seek method (keyset pagination)” that doesn’t use OFFSET is effective performance‑wise. This approach retrieves the next page based on the last record of the previous page, eliminating the need for skip processing. It is especially effective for large datasets.

Example of Using the Seek Method

With the seek method, you retrieve data based on key values such as the last id of the previous page, without using OFFSET. This allows the database to fetch the target rows directly, improving efficiency as you move to later pages.

Example

The following query retrieves the next set of rows using the last id obtained from the previous page as a reference.
SELECT * FROM table_name WHERE id > last_id_of_previous_page ORDER BY id ASC LIMIT 10;
With this approach, each query result starts from the last row of the previous page, resulting in a significant performance improvement.

Seek Method Using an Alternate Key

In some cases, pagination can be performed using columns other than id (for example, a date column). When using a date, you can advance pages by retrieving data after a specified timestamp. This enables the seek method to be used even when the column is not a primary key.

5. Best Practices for Using LIMIT and OFFSET

LIMIT and OFFSET can be used efficiently by following several best practices. Below are the key points and an explanation of how to maximize performance.

Apply Indexes

LIMIT and OFFSET when used, setting indexes that match the search criteria allows the database to search efficiently. In particular, indexing columns that are frequently used for pagination enables rapid data retrieval even with large datasets.

Base on Primary Key

Using the primary key for data retrieval in pagination allows the index to work efficiently, enabling the database to process queries quickly.

Adopt Seek Method

When dealing with large amounts of data, using the seek method instead of OFFSET maintains performance even on later pages. This approach is especially effective for handling massive datasets.

6. Summary

MySQL’s LIMIT and OFFSET are powerful tools for efficiently retrieving data. However, depending on the data volume and retrieval method, there is a risk of performance degradation. It is important to maintain performance while implementing pagination efficiently by using proper indexing and seek methods. When dealing with large datasets, consider adopting a seek method.