Speed up your pagination with a simple hack…

Before I go into the example in this little post, let me just say that this situation won’t be applicable to everyone…

But let’s consider the following:
We have a table with tens of thousands of records, that need to be paginated.

As you know, cake will execute two queries; first to get the count of total records, second to get the actual records.

The questions one might ask are:
“Would any user really go through 5 thousand pages to find what they are looking for?”
“If I bring the last 1,000 records wouldn’t that be enough for a vast majority of needs?”
“How many pages in Google do you go through, when searching for something, before you give up?”
“Is it not better to provide filters, or search tools to help your users narrow down the results to something manageable?”

If you’ve answered “Yes” to two or more questions, please consider the hack…

In your model, which needs to be paginated, do the following:

public function paginateCount($conditions = null,
                                 $recursive = 0,
                                 $extra = array()) {
   return 1000;
}

Yep, we are overriding paginateCount() and simply returning 1,000 because we know that this will be the maximum amount of records that our paginator needs to know about.
Depending on how complex the underlying query is (for example you might have JOIN’s or various conditions, which would usually need to be taken into the account in your typical count query), the above hack can dramatically increase the performance of your pagination.

m4s0n501
  • berclausen

    That reminds me of an optimization that can be done when paginating, specifically for the “count query”, and only for MySQL/MySQLi drivers.

    The full ticket is here: http://cakephp.lighthouseapp.com/projects/42648/tickets/1083-dbo_sourcephp-should-honor-order-by-null

    Briefly, when you query with a GROUP BY clause and no ORDER BY clause, MySQL uses the GROUP BY clause to sort the results (http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html), so if a query includes GROUP BY but you want to avoid the overhead of sorting the result (the first pagination query :P), you can suppress sorting by specifying ORDER BY NULL.

    CakePHP does not support ‘ORDER BY NULL’ to be created from ORM the way you expect so you must do something like this to achieve the goal:

    $db =& ConnectionManager::getDataSource($this->Model->useDbConfig);
    $expr = $db->expression(‘NULL ASC’);
    $query[‘order’] = array($expr);

    so that an expression object is created and the dbo will honor the ‘ORDER BY NULL’ clause:
    Array
    (

    [0] => stdClass Object
    (
    [type] => expression
    [value] => NULL ASC
    )

    )
    Believe me, when you have lots of rows, this is a huge HUGE optimization :D

  • Pingback: Speed up your pagination with a simple hack… | deepinphp.com()

  • teknoid

    @berclausen

    Also an excellent suggestion for complicated cases.
    Thank you for sharing.

  • berclausen

    yup, sorry
    $expr = $db->expression(‘NULL ASC’);
    should read:
    $expr = $db->expression(‘NULL’);

  • http://www.davidejones.co.uk David Jones

    Hmm thats cool, maybe one day i’ll have that many rows to make use of this heh

  • http://subin.me/ Subin George

    This is cool suggestion, But while reading this I came across few suggestions

    Most of the time number of recorders found is USP(unique selling point) for many listing portals,

    Like “10,000 results found” will be treated as better site than “1000 records found” site.

    That’s how we used to get suggestions from client,

    So I suggest, we can do a Cache for number records for first query for a day/week/month(based on the application needs) because change of number by 1 or 2 will not make any huge difference.

    • http://subin.me/ Subin George

      Yes You are right,

      We can achieve same by Overriding paginateCount in corresponding model or behavior with cache or similar actions,