Nisarg Upadhyay
What is pagination: Limit 10, offset 10

Learn MySQL: What is pagination

July 27, 2020 by

In this article, I am going to explain that in MySQL, what is pagination and how we can implement it. When we are populating a large dataset from the MySQL database, it is not easy to read all the records on the same page. Using pagination, we can divide the result-set into multiple pages, and that increases the readability of the result set. In this article, I am going to explain:

  1. How to implement pagination with example
  2. How to calculate the OFFSET using Page Number
  3. The performance overheads and how to deal with it

For the demonstration, I have installed MySQL on my workstation. You can walk through one of my previous articles, How to install MySQL database server 8.0.19 on Windows 10, that explains how we can install MySQL on window 10. I have imported a sample database named sakila in the MySQL database server. You can add the sample database while installing the MySQL database server.

How to implement pagination

We can implement pagination by using the LIMIT clause in the SELECT query. Following is the syntax:

LIMIT Clause

In syntax, the first argument is the LIMIT clause. This LIMIT clause is used to restrict the number of rows to be returned by the SQL Query. It is like the TOP clause in SQL Server.

For example, I want to return the top 20 actors. The output must be sorted based on the actor_id column, and the order must be ascending. Following is the query:

Below is the output of the query:

What is pagination: LIMIT clause

OFFSET Clause

If you notice the syntax again, the second argument is OFFSET. It is used to view a specific number of rows; for example, in a query output, you want to see the records between 10 and 20, then you can use OFFSET. It populates all the records of the table, and it discards the previous records that are defined in the OFFSET clause.

For example, we want to display the top 20 names of actor and the value of the actor_id column must start from 11 then you can write the query as below:

Following is the output:

What is pagination: OFFSET clause

As you can see in the above image, the value of the actor_id column starts from 10, and it has returned a total of 20 records.

How to calculate the OFFSET using Page Number

To calculate the number of the pages, you can hardcode the value of LIMIT and OFFSET, or you can pass the value of page number in the variables. We can calculate the value of OFFSET using the value of page number. It is a little bit more secure because we are passing the value of page number, and someone cannot create a page that has all the records by manipulating the value of the input variable.

The following is the formula:

Records_Per_page= 10;
offset_value = (page_Number-1) * Records_Per_page;

For example, you want to display the name of 30 actors in 3 pages. Therefore, the formula must be as shown below:

Records_Per_page= 10;
offset_value_1 = (1-1) * 10;
offset_value_2 = (2-1) * 10;
offset_value_3 = (3-1) * 10;

The value of Records_per_pages is used in the LIMIT clause and the values of offset_value_1, offset_value_2, offset_value_3 is used in the OFFSET clause.

The query should be written as follows:

Following is the output of the first query:

What is pagination: Limit 10, offset 0

Following is the output of the second query:

What is pagination: Limit 10, offset 10

Following is the output of the third query:

What is pagination: Limit 10, offset 20

The performance overheads

The pagination is very useful when you want to design an application that displays a large dataset into multiple pages. While designing the application, we populate the entire dataset from the database server and then perform pagination on the application/web server. Now, when you perform this on the database server, you can achieve performance improvement. But sometimes it becomes an additional overhead on the database server. Especially when you are sharing the same database server between multiple client databases, to avoid the performance issue, we can use the following alternative.

Implement the paging using ORDER BY (avoid discarding the records)

As mentioned, the pagination populates all the records of the table, displays the number of records specified in the LIMIT clause, and discards all the previous records specified in the OFFSET clause. If the table contains millions of records, then the performance of the query can be reduced significantly. To avoid that, we can specify the range in the WHERE clause and limit the records using the LIMIT clause. For example, we want to populate the name of the top ten actors whose actor_id is greater than 10. The query should be written as follows:

Below is the output:

What is pagination: Without OFFSET

Summary

The pagination is a very interesting concept in MySQL and can be achieved easily by LIMIT and OFFSET clauses. It is very useful when you want to display the large recordset on multiple pages. In this article, I have explained about the pagination and how we can implement it, how we can calculate the OFFSET using page number, and the number of records per page. I have also explained how it can impact the performance and the possible alternative to it.

Table of contents

Learn MySQL: Querying data from MySQL server using the SELECT statement
Learn MySQL: What is pagination
Learn MySQL: Sorting and Filtering data in a table
Learn MySQL: Add data in tables using the INSERT statement

Nisarg Upadhyay
MySQL

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

120 Views