How to retrieve the total number of rows when using LIMIT X, XXX
Posted by yangyang2036, 09-17-2008, 10:09 PM With php+mysql, $results -> num_rows returns the number of rows of the current query, however with LIMIT X, XXX in that query the $results -> num_rows only contains the LIMITED number of rows rather than the total amount of rows meeting the WHERE clause without LIMIT. e.g. With LIMIT 0, 100 in my query $results -> num_rows returns 100 rather than 2000 which is the total number of rows had I not limited the returning results for paging. Some search site has the capability of paging big amount of results while displaying also the total amount. Does it require 2 or more queries to do this? Is it possible to do it with just 1 query? So what's the most efficient way to achieve this:
Posted by larwilliams, 09-17-2008, 10:24 PM Normally, it uses a 2nd query such as: SELECT COUNT(*) as Total FROM table Modified appropriately, it will give the number of rows in a search/table.
Posted by Xeentech, 09-18-2008, 07:46 AM Without some very spesific code, and some mods to PHPs interface, you will need to make two queries. Depending on your setup and indexes the above solution might be slower than the SQL_CALC_FOUND_ROWS / FOUND_ROWS() method. For more see: MySQL :: MySQL 5.0 Reference Manual :: 11.10.3 Information Functions
Posted by yangyang2036, 09-21-2008, 09:26 PM Thank you both, FOUND_ROWS is a great way to do it. However this also arouses my concern about the expenses of getting the total number of rows meeting the criteria. Doesn't MySQL have to perform all the algorithmic comparing actions needed just as when it's returning all results this way, rather than the first 100? So basically it is the same with just 1 query that's without LIMIT clause.