Pagination Using MySQL LIMIT, OFFSET

Pagination enables users to view a limited set of records per page. It is very useful in database-driven applications as it makes it easier for users to view and understand information.

MySQL provides a powerful mechanism for achieving pagination using the LIMIT and OFFSET clauses. This guide will explore the syntax of pagination, understand its application, and present practical examples to demonstrate how LIMIT and OFFSET can be utilized for efficient data retrieval in a paginated format.

Pagination Using MySQL LIMIT, OFFSET

Pagination is an important tool for organizing large data sets into manageable chunks. It is often used in web applications. In MySQL, pagination is supported by the LIMIT and OFFSET clauses.

The basic syntax for using LIMIT and OFFSET in MySQL for pagination is as follows:

Syntax

SELECT * FROM your_table
LIMIT number_of_records OFFSET offset_value;

Key Points:

• LIMIT: Specifies the number of records to retrieve.
• OFFSET: Specifies the starting point for records retrieval.

Demo MySQL Database

For this tutorial on Pagination in MySQL, we will use the following table in examples.

product_id	product_name
1	Laptop
2	Smartphone
3	Tablet
4	Desktop
5	Printer
6	Monitor

To create this table on your system, write the following MySQL queries.

MySQL

CREATE DATABASE Increment; 
USE Increment; 
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50)
);

INSERT INTO products (product_id, product_name)
VALUES 
(1, 'Laptop'),
(2, 'Smartphone'),
(3, 'Tablet'),
(4, 'Desktop'),
(5, 'Printer'),
(6, 'Monitor');

Pagination Using MySQL LIMIT and OFFSET Example

Let’s see some examples on how to implement pagination using MySQL LIMIT and OFFSET clause

Example 1: Simple Pagination

The pagination query retrieves the first 5 records from the products table, simulating the data for the first page.

SELECT * FROM products
LIMIT 5 OFFSET 0;

Output:

query Output

Explanation: The query retrieves the first five records from the ‘products‘ table using LIMIT and OFFSET. The result displays product information such as ‘product_id‘ and ‘product_name‘ for the specified range, starting from the first record (OFFSET 0).

Example 2: Advanced Pagination with Page Number

In this example, similar to the first one, we create a table named products and insert some sample data. The advanced pagination query uses variables to represent the desired page number (@page_number) and the number of records per page (@records_per_page). It fetches the records for the specified page, taking into account the offset based on the page number.

SET @page_number = 2;
SET @records_per_page = 5;

-- Use Dynamic SQL
SET @query = CONCAT('SELECT * FROM products LIMIT ', @records_per_page, ' OFFSET ', (@page_number - 1) * @records_per_page);

-- Execute the Query
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Output:

Query Output

Explanation: The provided SQL code dynamically generates a paginated query for the ‘products‘ table based on the specified page number and records per page. The query retrieves records for the second page with a limit of five records per page. Using dynamic SQL enhances flexibility, allowing adjustments to the query structure based on variable inputs. The result displays the relevant product information for the specified pagination parameters.

Conclusion

The use of LIMIT and OFFSET in MySQL is crucial for optimizing pagination in database applications. These clauses provide a simple and versatile method for retrieving precise records, enabling the seamless implementation of paginated views.

Developers can enhance user experience by organizing and displaying data in a paginated format, making applications responsive and user-friendly. This allows them to manage extensive datasets with ease, making them an essential tool in database development.