Zend_Paginator; is it optimized?

Question

I am about to use Zend_Paginator class in my project. I found examples of the class on the internet. One of them is

$sql = 'SELECT * FROM table_name ';    
$result = $db->fetchAll($sql);    
$page=$this->_getParam('page',1);    
$paginator = Zend_Paginator::factory($result);   
 $paginator->setItemCountPerPage(10));   
 $paginator->setCurrentPageNumber($page);   
 $this->view->paginator=$paginator;

on the first line, it actually select all the rows from table_name. What if I have a table with 50000 rows? That would be very inefficient.

Is there any other way to use Zend Paginator?

Answer 1

About this problem, you might be interested by this section of the manual : 39.2.2. The DbSelect and DbTableSelect adapter, which states (quoting, emphasis mine) :

... the database adapters require a more detailed explanation.
Contrary to popular believe, these adapters do not fetch all records from the database in order to count them.

Instead, the adapters manipulates the original query to produce the corresponding COUNT query.
Paginator then executes that COUNT query to get the number of rows.

This does require an extra round-trip to the database, but this is many times faster than fetching an entire result set and using count().
Especially with large collections of data.

(There is more to read on that page -- and there is an example that should give you more information)

The idea is that you will not fetch all data yourself anymore, but you'll tell to Zend_Paginator which Adapter it must use to access your data.

This Adapter will be specific to "Data that is fetched via an SQL query", and will know how to paginate it directly on the database side -- which means fetching only what is required, and not all data like you initialy did.

Answer 2

I recommend passing a Zend_Db_Select object as Zend_Paginator::factory($select); rather than a passing a result rowset. Otherwise, you're selecting the entire result set and then doing the pagination. In your current solution, if you had a million rows, you'd select all of them before getting the chunk of rows defined by the current page.