I am trying to split results comming from a mysql db with php into more html pages. Example: Results 1-10 out of 100
Therefor I limit the sql statement with: limit 1, 10
The sql statement is very complex. So far I have always used the same statement but without the limit and with a count inside to get the total number of results.
Now with this statement it would be a huge decrease in performance. Is there a way to work with the limit statement, but to get the total number of results?
As far as I know the LIMIT command only limits the visiblity, but mysql queries all the data.
What version of MySQL? If 4.0+ you could use
SELECT SQL_CALC_FOUND_ROWS column1, column2, ... FROM TABLE WHERE ... LIMIT x,y
to get the rows you want to retrieve and then use
SELECT FOUND_ROWS()
to get the total number of rows there would have been without the limit. Still two queries (no way around that, anyhow), but hopefully optimized better by MySQL. Perform your own tests, of course.
See the documentation on FOUND_ROWS() at the following URL for more info: http://dev.mysql.com/doc/mysql/en/Information_functions.html
--
---John Holmes...
Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/
php|architect: The Magazine for PHP Professionals – www.phparch.com
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
