|
Custom Search
| |
|
Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL In our Northwind database, the orders table contains 830 records. It would be impractical and extremely slow to display all these orders together on one web page. The best practice is to go through them page by page which displays, for example, 10 orders per page. The page links would look like Page 1, 2, 3.... or Prev Page and Next Page. 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.
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.
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 |
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |