Custom pagination query in CakePHP

My pet peeve list seems to grow on a regular basis.  One of them is poorly optimized code.  Not only is the code I’m going to discuss poorly optimized, it’s from the documentation of CakePHP!

If you search for creating custom pagination in CakePHP, you will find quite a few results, potentially this one.  The one that I would assume the best would be the one from CakePHP itself, but it’s not and let me show you why.

First, here is the link describing custom pagination:
http://book.cakephp.org/view/249/Custom-Query-Pagination

To accomplish pagination with custom code, it’s quite simple.  In your model you create two functions:

1. function paginate($conditions, $fields, $order, $limit, $page = 1, $recursive = null, $extra = array())
2. function paginateCount($conditions = null, $recursive = 0, $extra = array())

Inside of those function contains your custom queries that you would like to perform.  This can be a $this->query or a $this->find, whatever suits your needs.

If you continue to read the documentation, inside the paginateCount function, the example does a standard query and returns the count of results!  Not only that it has to use a DISTINCT!  The combination of these two items could not make your pagination count slower.  I suppose in the example they use, the distinct is kind of important, but returning all results and retrieving the count is not.

Does it achieve the correct results?  Yes, it does, but you can achieve the same results in 10 times less the time, if not 100 times less depending on the size of the data.

The example from CakePHP must first return the data from the database server, if the record size is large enough this can take time.  Next CakePHP, needs to perform it’s regular post query functions on the data.  Again, large record sets take time.

Instead, if you simply use a SELECT COUNT() function, the database server only has to return 1 record and CakePHP only needs to process one record.

I saw this example used in an ASP.NET project where a page would take over 2 minutes to load because it was returning a count of over 10,000 records.  This is not CakePHP related, simply database related.  But changing that query from returning the number of records to a COUNT() made the page load instaneously!  Over two minutes to instant, I think the results speak for themselves.

To conclude, if you wish to use custom pagination in CakePHP, it’s a great idea if you can’t accomplish it with the built-in tools with CakePHP, but ensure you use a SELECT COUNT() instead of returning the count of records!