Pagination Queries ​

When using SELECT queries, if the result set contains a large amount of data, such as tens of thousands of rows, displaying all data on one page can be overwhelming. Instead, it’s better to display the data in pages, showing a limited number of rows at a time, for example, 100 rows per page.

Pagination effectively means extracting records from the result set in a specific range, such as displaying records 1 to 100 as the first page, 101 to 200 as the second page, and so on.

To implement pagination, we can use the LIMIT <N> OFFSET <M> clause, where <N> is the number of records to return and <M> is the number of records to skip. First, we can sort all students by their scores in descending order:

-- Sort by score in descending order:
SELECT id, name, gender, score FROM students ORDER BY score DESC;

Now, to paginate the results, let’s assume we want to display 3 records per page. To get records for the first page, we can use:

-- Query for the first page:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 0;

In this query, LIMIT 3 OFFSET 0 means to start from record 0 and return up to 3 records. Note that SQL indexing starts at 0.

To query the second page, we just need to skip the first 3 records by setting OFFSET to 3:

-- Query for the second page:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 3;

Similarly, to query the third page, set OFFSET to 6:

-- Query for the third page:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 6;

For the fourth page, set OFFSET to 9:

-- Query for the fourth page:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 9;

If there is only one record for the fourth page, the result will show just that one record, as LIMIT 3 specifies a maximum of 3 records.

The key to pagination is first determining the number of results to display per page (here, it’s 3), and then using the current page index (starting from 1) to set the values for LIMIT and OFFSET:

• LIMIT is always set to the page size.
• OFFSET is calculated as pageSize * (pageIndex - 1).

This approach allows for the correct retrieval of records for the Nth page.

If the original result set contains only 10 records but we set OFFSET to 20, what will happen?

-- OFFSET set to 20:
SELECT id, name, gender, score
FROM students
ORDER BY score DESC
LIMIT 3 OFFSET 20;

Setting OFFSET beyond the total number of records will not result in an error; instead, it will return an empty result set.

Note ​

OFFSET is optional. If you only write LIMIT 15, it’s equivalent to LIMIT 15 OFFSET 0.

In MySQL, LIMIT 15 OFFSET 30 can also be simplified to LIMIT 30, 15.

When using LIMIT <M> OFFSET <N> for pagination, efficiency may decrease as N increases.

Consideration ​

How can we calculate the total number of pages before performing pagination queries?

Summary ​

Using LIMIT <M> OFFSET <N> allows for pagination of the result set, returning only a portion of the data at a time. Pagination requires determining the number of records per page and the current page number to set the appropriate values for LIMIT and OFFSET.