To accomplish this task, in the backend, MySQL provides LIMIT, SQL_CALC_FOUND_ROWS, and FOUND_ROWS() function for us to obtain (1) only a small amount of records needed for each page; (2) the number of all records in total if the LIMIT clause were not used, and therefore the total number of pages can be calculated, given that x number of records per page is predefined.

Alternatively, you can resort to MySQL's LIMIT and OFFSET clause to accomplish the same thing. So there are two techniques in MySQL that can be used for web pagination.

1. Use LIMIT (optionally OFFSET), and SELECT COUNT(*)
2. Use LIMIT, SQL_CALC_FOUND_ROWS, and FOUND_ROWS()

There are pros and cons for each technique, especially pay attention here.

In this tutorial, we focus on the second technique above.

SQL_CALC_FOUND_ROWS is a MySQL query modifier which tells MySQL to calculate how many rows the query returns if LIMIT clause is not used. FOUND_ROWS() function returns the number of rows by running query SELECT FOUND_ROWS(). FOUND_ROWS() gives you the total number of rows in the full result set without running another SELECT COUNT(*) query.

The basic SQL syntax is (1) run SELECT query with SQL_CALC_FOUND_ROWS; (2) then run another SELECT query with FOUND_ROWS.

SELECT SQL_CALC_FOUND_ROWS * 
FROM tbl_name LIMIT x;

SELECT FOUND_ROWS();

If FOUND_ROWS() is used without the presence of SQL_CALC_FOUND_ROWS, it returns the number of rows from the previous SELECT statement.

Practice #1:

In the two queries below, the first query returns row 1 to row 10 from orders table. The second query returns the total number of rows in orders table if LIMIT is not used in the first query.

By using FOUND_ROWS() and given the number of records per page, we can calculate how many pages to navigate through for all the 830 orders. If we display 10 orders per page, there would be 83 pages to go through in order to finish viewing all of the 830 orders.

-- Page 1: starting from the first record and return 10 records only.
SELECT SQL_CALC_FOUND_ROWS * FROM orders 
LIMIT 0, 10;

-- Page 1: Return total number of records
SELECT FOUND_ROWS();
 
-- Page 2: starting from the 11th record and return 10 records only.
SELECT SQL_CALC_FOUND_ROWS * FROM orders 
LIMIT 10, 10;

-- Page 2: Return total number of records 
SELECT FOUND_ROWS();

-- Page 3: starting from the 21st record and return 10 records only.
SELECT SQL_CALC_FOUND_ROWS * FROM orders 
LIMIT 20, 10;

-- Page 3: Return total number of records
SELECT FOUND_ROWS();

Query 1 (1st part): rows 1 to 10 are returned

Query 1 (2nd part): FOUND_ROWS() returns 830 records

Practice #2: Use UNION

When using SQL_CALC_FOUND_ROWS with UNION clause, The SQL_CALC_FOUND_ROWS keyword must appear in the first SELECT of the UNION, see example below.

In this query, the UNION combines 'Seafood' products with 'Meat/Poultry' products and only 5 random rows are returned each time, and FOUND_ROWS() returns 18 which is the total number products in 'Seafood' and 'Meat/Poultry' category.

-- 5 random records 
SELECT SQL_CALC_FOUND_ROWS p.ProductID, p.ProductName, c.CategoryName 
FROM products as p 
JOIN categories as c on p.CategoryID=c.CategoryID 
WHERE c.CategoryName='Seafood' 
UNION ALL
SELECT p.ProductID, p.ProductName, c.CategoryName 
FROM products as p 
JOIN categories as c on p.CategoryID=c.CategoryID 
WHERE c.CategoryName='Meat/Poultry'
ORDER BY RAND()
LIMIT 0, 5;

-- Get total number of records if LIMIT clause is not used.
SELECT FOUND_ROWS();

5 randowm records returned:

1 record returned:

Happy Coding!

Other tutorials in this category

1. Using String Functions, Part 1

2. Using String Functions, Part 2

3. Using String Functions, Part 3

4. Using MySQL Date and Time Functions, Part 1

5. Using MySQL Date and Time Functions, Part 2

6. Using MySQL Date and Time Functions, Part 3

7. Using MySQL Control Flow Functions

8. Using MySQL Conversion Functions

9. Using MySQL Comparison Functions

10. Using Aggregate Functions in MySQL

11. MySQL RANK() function

12. MySQL DENSE_RANK() function

13. MySQL ROW_NUMBER() function

14. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function

15. Using LIMIT and OFFSET to Page Thru Query Result in MySQL

16. Use LEAST function to find the SMALLEST value from multiple arguments

17. How to make case-sensitive comparison in MySQL

18. Use GREATEST function to find the LARGEST value from multiple arguments

19. MySQL MID() function with examples

20. MySQL LOCATE() function with examples

21. MySQL SUBSTR() function with examples

22. MySQL POSITION() function with examples

23. How to use MySQL REPLACE function in SELECT and UPDATE queries

Back to Tutorial Index Page