Zend_Paginator - Increase querys

Question

Hello,

I started using Zend_Paginator, it works everything fine but I noticed that there is one more query which slows the load time down. The additional query:

SELECT COUNT(1) AS `zend_paginator_row_count` FROM `content`

The normal query:

SELECT `content`.`id`, `content`.`name` FROM `content` LIMIT 2

PHP:

$adapter = new Zend_Paginator_Adapter_DbSelect($table->select()->from($table, array('id', 'name')));
$paginator = new Zend_Paginator($adapter);

Could I merge the two querys into one (for better performance)?

Answer 1

Make sure you have an index on one or more int-based columns of the selected table. So the count query will not have much of a performance inpact. You can use setRowCount() to provide the count (if you have it).

From http://framework.zend.com/manual/en/zend.paginator.usage.html :

Note: Instead of selecting every matching row of a given query, the DbSelect and DbTableSelect adapters retrieve only the smallest amount of data necessary for displaying the current page. Because of this, a second query is dynamically generated to determine the total number of matching rows. However, it is possible to directly supply a count or count query yourself. See the setRowCount() method in the DbSelect adapter for more information.

Answer 2

Merging the two wouldn't really have much of a performance increase if any. The actual select content has the limit statement in it (as you are trying to get a subset of the entire table in the database) where the count needs to count all rows in the database. The reason it is done like this is to prevent having to select a very large set of data simply to get the count.