Zend_Paginator Is it optimized?

Question

I am going to use the Zend_Paginator class in my project. I found class examples on the Internet. One of them -

$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; 

in the first row, it actually selects all the rows from table_name. What if i have a table with 50,000 rows? That would be very inefficient.

Is there any other way to use Zend Paginator?

Answer 1

You may be interested in this topic in this section of the manual: 39.2.2. DbSelect and DbTableSelect adapter , which says (quoting, emphasis mine):

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

Instead, adapters manipulate the original request to obtain the appropriate COUNT . Paginator then executes that COUNT request to get the number of rows.

This requires additional round-trip to the database, but it is many times faster than fetching the whole set of results and using count() .
Especially with large data collections.

(Read more on this page - and there is an example that should give you more information)

The idea is that you will no longer receive all the data , but you will tell Zend_Paginator which adapter it should use to access your data.

This adapter will be specific to "Data that is retrieved using the SQL query", and will know how to split it into a straight line on the database side, which means getting only what you need and not all the data like you.

Answer 2

I recommend passing the Zend_Db_Select object as Zend_Paginator::factory($select); instead of passing a set of rows of results. Otherwise, you select the entire result set and then paginate. In your current solution, if you had a million lines, you should select all of them before getting a piece of the lines defined by the current page.