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:
- How to implement pagination with example
- How to calculate the OFFSET using Page Number
- 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:
|
1 |
Select * from <table_name> LIMIT value_1, OFFSET value_2 |
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:
|
1 |
select * from actor order by actor_id asc LIMIT 20 |
Below is the output of the query:
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:
|
1 |
select * from actor LIMIT 20 OFFSET 10; |
Following is the output:
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:
|
1 2 3 |
select * from actor LIMIT 10 OFFSET 0; select * from actor LIMIT 10 OFFSET 10; select * from actor LIMIT 10 OFFSET 20; |
Following is the output of the first query:
Following is the output of the second query:
Following is the output of the third query:
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:
|
1 |
select * from actor where actor_id > 10 LIMIT 10; |
Below is the output:
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
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
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022
- Manage SQL Databases in CentOS: Manage filegroups of user databases - October 5, 2022