Zend Framework: Need advice on implementing Zend_Paginator_Adapter count()

Question

i want to implement my own Zend_Paginator_Adapter so i implemented Zend_Paginator_Adapter_Interface (docs). i am now at implementing count(). am i right to say that if my query is

SELECT * FROM Posts LIMIT ... // where limit is for pagination purposes

i need to get the total number of rows in the table (to return in count())?

SELECT COUNT(*) FROM Posts

it seem unavoidable to have 2 queries? i am not too concerned in my current project about performance, but the need for 2 queries, just made me think, so i wanted to just ask here

Answer 1

am i right to say...to get the total number of rows in the table (to return in count())?
SELECT COUNT(*) FROM Posts

This is certainly one way to get the result you are looking for. Running a second query is not that bad assuming your table/indexes are optimized.

There is an alternative. You can include the statement "SQL_CALC_FOUND_ROWS" in your original query, then your second query would consist of only:

"SELECT FOUND_ROWS();"

See the following link for more info on this functionality: http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Of course, this assumes you are using MySQL.

i am not too concerned in my current project about performance, but the need for 2 queries, just made me think...

It is certainly a valid concern especially as the number of records starts to increase. What makes Zend_Paginator awesome is the fact that you can inject a Zend_Cache_Core instance. This will ensure that you will only be running these queries a minimal number of times based on how long your cache lifetime is.

See the following page for more info on the caching features: http://framework.zend.com/manual/en/zend.paginator.advanced.html#zend.paginator.advanced.caching