Page MySQL Data with a Limit Clause

Introduction

Pagination is the process of sub-dividing a large recordset into multiple manageable pages. In MySQL, you can implement pagination by viewing only a specific number of rows in each query output. You can achieve this by using the LIMIT clause.

Quite often, you need to do this when designing MySQL applications in order to enhance user experience and improve database performance.

For instance, if a customer requests all the products in your store and you've thousands of products, you should divide these items into multiple pages to avoid overcrowding the customer's web browser or slowing the page's speed.

In this guide, you'll use MySQL LIMIT clause to page and provide navigation for items in a products table.

Prerequisites

Before you begin, make sure you have the following:

• A MySQL database server. Although this guide is tested on an Ubuntu VPS, you're free to use any Operating System that supports the MySQL database.

Create a Sample Database

Connect to your server and log in to the MySQL database server as root.

$ sudo mysql -u root -p

When prompted, type the root password of your MySQL server and hit Enter to proceed. Then, create an online_shop database.

mysql> CREATE DATABASE online_shop;

Select the online_shop database.

mysql> USE online_shop;

Create a Sample Table

Once the database is in place, you'll start off by creating a products table. You'll use this table to store a list of all items that you are offering to your customers. The products will be uniquely identified by the sku column and a descriptive product_name field.

Create the table.

mysql> CREATE TABLE products (
       sku INT AUTO_INCREMENT PRIMARY KEY, 
       product_name VARCHAR(50),        
       retail_price DOUBLE
       );

Populate the products table with some records. In a fully functional online store, you might have hundreds or even thousands of products to sell. However, to make this guide easy to follow, you'll populate the table with just 7 products. So ran the following INSERTS commands one by one.

mysql> INSERT INTO products (product_name, retail_price) VALUES ('DENIM TROUSEr', '18.20');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('ELEPHANT PRINTED SHIRT', '24.50');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('DOUBLE-BRAIDED SOCKS', '3.19');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('LAPTOP BAG', '35.00');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('STRIPPED SWEATER', '19.89');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('MEN COTTON VEST', '1.23');
mysql> INSERT INTO products (product_name, retail_price) VALUES ('KITCHEN TOWEL', '2.00');

Query the products table to check if the items were inserted successfully.

mysql> SELECT
       sku,
       product_name,
       retail_price          
       FROM products;

The products should be listed as shown below.

+-----+------------------------+--------------+
| sku | product_name           | retail_price |
+-----+------------------------+--------------+
|   1 | DENIM TROUSEr          |         18.2 |
|   2 | ELEPHANT PRINTED SHIRT |         24.5 |
|   3 | DOUBLE-BRAIDED SOCKS   |         3.19 |
|   4 | LAPTOP BAG             |           35 |
|   5 | STRIPPED SWEATER       |        19.89 |
|   6 | MEN COTTON VEST        |         1.23 |
|   7 | KITCHEN TOWEL          |            2 |
+-----+------------------------+--------------+
7 rows in set (0.00 sec)

In the output above all the products are displayed without any form of pagination. If you query a table with thousands of records, your computer screen might freeze and you might find it hard navigating through the products intuitively. To overcome this challenge, you'll page your data in the next step.

Page the MySQL Recordset

In MySQL, the best way you can achieve pagination is by implementing the LIMIT clause. When you use it in a SELECT statement, the LIMIT clause simply selects a limited number of records depending on the use-case of your application. In most applications, you might find it suitable to list items in batches of 25, 50, or even 100 records.

In this guide, you'll list 3 products per page to see how the logic works. The formula for implementing pagination in MySQL is shown below:

TOTAL PAGES = CEILING( [TOTAL COUNT OF PRODUCTS] / [NUMBER OF RECORDS PER PAGE])

In this case, you've 7 total products and the desired number of records per page is 3. The CEILING() function rounds the total pages to the nearest whole number since you can't have a fraction of a page. So, if you put the formula above into action, the equation for the 7 records in your table should look as follows:

TOTAL PAGES = CEILING(7 / 3) 
TOTAL PAGES = CEILING(2.33)
TOTAL PAGES  = 3

You can confirm the maths above by running the query below in the MySQL command-line interface:

mysql> SELECT CEILING(7 / 3) ;

Output.

+----------------+
| CEILING(7 / 3) |
+----------------+
|              3 |
+----------------+
1 row in set (0.01 sec)

So, in total, you'll require 3 pages to display your 7 products in batches of 4.

The next thing is throwing the LIMIT clause into the equation. Basically, to return only a few records from a recordset using the LIMIT clause, you should use the syntax shown below:

SELECT column_1, column_2, column_n FROM [TABLE NAME] LIMIT [OFFSET], [RECORDS PER PAGE]

The [OFFSET] value determines the number of records to skip. In MySQL, the formula for calculating the OFFSET value is shown below:

OFFSET = (CURRENT PAGE - 1) * NUMBER OF RECORDS PER PAGE;

So, to retrieve the items for page 1 in your products table, use the formula below.

OFFSET = (1 - 1) * 3;
OFFSET = (0) * 3;
OFFSET = 0;

On page 1 you're skipping 0 records since the offset results to 0. The full SQL syntax for page 1 therefore becomes

mysql> SELECT
       sku,
       product_name,
       retail_price          
       FROM products
       LIMIT 0, 3;

Output for page 1:

+-----+------------------------+--------------+
| sku | product_name           | retail_price |
+-----+------------------------+--------------+
|   1 | DENIM TROUSEr          |         18.2 |
|   2 | ELEPHANT PRINTED SHIRT |         24.5 |
|   3 | DOUBLE-BRAIDED SOCKS   |         3.19 |
+-----+------------------------+--------------+
3 rows in set (0.00 sec)

Next, use the formula below to retrieve page 2 products:

OFFSET = (CURRENT PAGE - 1) * NUMBER OF RECORDS PER PAGE;
OFFSET = (2 - 1) * 3;
OFFSET = (1) * 3;
OFFSET = 3;

Just like on the first page, the complete SQL syntax for page 2 is shown below. Please note, on page 2, you're instructing MySQL to skip 3 products.

mysql> SELECT
       sku,
       product_name,
       retail_price          
       FROM products
       LIMIT 3, 3;

When you run the query for page 2, you'll get the output shown below.

+-----+------------------+--------------+
| sku | product_name     | retail_price |
+-----+------------------+--------------+
|   4 | LAPTOP BAG       |           35 |
|   5 | STRIPPED SWEATER |        19.89 |
|   6 | MEN COTTON VEST  |         1.23 |
+-----+------------------+--------------+
3 rows in set (0.00 sec)

To retrieve page 3 items, use the same formula that you've applied on page 1 and 2,

OFFSET = (CURRENT PAGE - 1) * NUMBER OF RECORDS PER PAGE;
OFFSET = (3 - 1) * 3;
OFFSET = (2) * 3;
OFFSET = 6;

So the OFFSET for page 3 is 6. In this case, MySQL will skip 6 records and display the rest of the items. The full SQL syntax for displaying page 3 therefore becomes.

mysql> SELECT
       sku,
       product_name,
       retail_price          
       FROM products
       LIMIT 6, 3;

Ensure you get the output shown below for page 3.

+-----+---------------+--------------+
| sku | product_name  | retail_price |
+-----+---------------+--------------+
|   7 | KITCHEN TOWEL |            2 |
+-----+---------------+--------------+
1 row in set (0.00 sec)

As you can see, only 1 item is listed on page 3 and this is the last page in your list. The outputs from all the 3 pages confirm that you're able to page MySQL data without any errors.

In a production environment, you might find it easier to implement the formulas in the scripting language that you're using to call MySQL queries(e.g. PHP, Python, Node.js, etc). The outputs for the pages will be the same provided you use the formulas as indicated in this guide.

Conclusion

This tutorial has taken you through the steps of paging MySQL data using the LIMIT clause. Feel free to extend this guide to suit your needs when designing applications that require pagination to improve user interaction.