Database: SQL Pagination - How many pages?

Question

I'm using MySQL LIMIT to display 50 rows (pages) at a time, like so:

SELECT cols
FROM table
LIMIT 49, 50

What's the most efficient way for me to determine how many "pages" I have in my result set (how many total records my query is producing)?

Before I run the code above, should I just run a quick and dirty:

SELECT COUNT(cols)
FROM table

to determine the # of rows in my result set?

(This seems kind of inefficient to run 2 queries to do this)

Answer 1

i think you are over optimizing. The only thing you can do to optimize the pagination process that i can think of is to keep the count in session, so you only do it once during page navigation, bad part it will be bad if new items are added during pagination.

Answer 2

One possibility (which might only work with some databases) is to peek inside the query plan to see what the optimizer is forecasting as a total row count. For example, in Oracle you can access the EXPLAIN plan and see that the optimizer estimates that, say, x rows are going to be returned in total. The accuracy of this of course depends on a number of factors: how appropriately the tables are indexed, how non-stale the statistics are etc.etc.

Answer 3

You can use FOUND_ROWS() function:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function%5Ffound-rows