Abysmal Performance for Count () using ZF2 Paginator on InnoDB Tables

Question

I am trying to use Paginator ZF2 on some large ones (about 10 million at worst without a search filter). My tables are in InnoDB format, which, as I understand it, does not save a specific account as part of the metadata.

I understand that I can extend the Zend \ Paginator \ Adapter \ DbSelect class and implement my own count () method, which uses the count data that I manually store in another table, but I'm not sure how to store the bills for all possible permutation searches that can be performed.

By default, the ZF2 DbSelect adapter uses this method:

<?php public function count() { if ($this->rowCount !== null) { return $this->rowCount; } $select = clone $this->select; $select->reset(Select::LIMIT); $select->reset(Select::OFFSET); $select->reset(Select::ORDER); $countSelect = new Select; $countSelect->columns(array('c' => new Expression('COUNT(1)'))); $countSelect->from(array('original_select' => $select)); $statement = $this->sql->prepareStatementForSqlObject($countSelect); $result = $statement->execute(); $row = $result->current(); $this->rowCount = $row['c']; return $this->rowCount; } ?> 

Here is a very simple example that the method produces for me:

 SELECT COUNT(1) AS `c` FROM ( SELECT `contacts`.`id` AS `id`, `contacts`.`firstname` AS `firstname`, `contacts`.`middlename` AS `middlename`, `contacts`.`lastname` AS `lastname`, `contacts`.`gender` AS `gender` FROM `contacts` WHERE `contacts`.`trash` = '0' ) AS `original_select` 

I'm not sure what the performance will be in MyISAM tables, but this does not help me because it absorbs all the free space on the Amazon RDS instance (25 GB, db.m1.small) on which it is running. For comparison, only the internal (initial) request is executed, it completes after 100 seconds (of course, not very well) and returns 7.39 million records.

Here is EXPLAIN from the internal query (EXPLAIN on the counter also dies due to disk space on the RDS server):

  + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------- + --------- + ------- +
 |  id |  select_type |  table |  type |  possible_keys |  key |  key_len |  ref |  rows |  Extra |
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------- + --------- + ------- +
 |  1 |  SIMPLE |  contacts |  ref |  trash |  trash |  1 |  const |  3441317 |  |
 + ---- + ------------- + ---------- + ------ + ------------ --- + ------- + --------- + ------- + --------- + ------- +
 1 rows in set (0.04 sec)

Is there anything that can be done to make this better? Is the way Paginator ZF2 handles considered incompatible in any way with what InnoDB does? How will others handle caching of all possible queries if we allow searches on most fields in the database?

Thanks in advance...

Answer 1

You do not need a choice from the original request - it consumes memory / disk space!

 SELECT count( 1 ) AS `c` FROM ( SELECT 1 FROM `contacts` WHERE `trash` = 0 ) AS `original_select` 

Besides:

• Assuming garbage is just a boolean, make it a boolean with a null value, and looking for int or boolean is true / false

   ALTER TABLE `contacts` CHANGE `trash` `trash` TINYINT( 1 ) NOT NULL 

• Be sure to index the basket column

   ALTER TABLE `contacts` ADD INDEX `TRASH` ( `trash` ) 

Further:

• Breaking up large result sets does not necessarily require a precise calculation: let's say we show 100 records per page, we do not need 100,000 single pages of N buttons. Instead, calculate the page using your offset and limit, and just show the single buttons, for example. previous / next 10 pages and combine them with some buttons "show next / prev 10 pages".

• When you need the "go to the last page" feature, why not use something like a DESC order to achieve something like this.

• Is there a situation where someone will split pages into your 10 meter lines? Maybe provide advanced filters to help the user find what he needs.

Answer 2

If you use this query instead:

 SELECT c from ( SELECT COUNT(1) AS c from contacts where trash = '0' ) AS original_select