Re: Re: Pagination Optimization

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



* Thus wrote Bruno B B Magalhes:
> Thanks for your help, in fact helped a lot... Now my function only 
> performs 2 queries, here it is:
> 
> =======================================
> 	function fetch_paginated($query='',$page=1,$itens=20)
> 	{
> 		$this->query($query);

This here is going to be your big bottle neck. You're requiring
your database to fetch all the results. You're goal with
pagination is to know basically how many rows there are from the
result with minimal effort there are two ways i know that can
accomplish this rather efficiently:

  1. use the SQL_CALC_FOUND_ROWS option (i'm assuming this is
     mysql from your LIMIT statement).

     This way you can always use the LIMIT based on the page and
     items you want, and still know how many items would have
     been in the results. This will result with simply one query
     to the database.

  2. Modify your select statement to issue a count(*) of the
     query, to accomplish this automatically you can do something
     like:


     /* find the coulumns to replace with count(*) */
     $match = '/(select)\s+(.*?)\s+(from\s+.*)/i';

     /* row to start on; calculating what page they are
      * on to the actual row number */
     $start = (($page-1) * $items);

     /* replace fieldnames with count(*) */
     $replace = '$1 count(*) as qty $3';

     /* now replace the sqlqty and make the limit query  */
     $sql_qty = preg_replace($match, $replace, $query);
     $sql_qty = preg_replace('/(order|group) by.*/', '', $sql_qty);
     $sql_limit = $query . " limit $start, $items";


     And now you have $sql_qty that returns the number of total
     rows are available and $sql_limit which give you the actual
     results.


The first usage, is probably the fastest approach (i havn't done
any benchmarks), but it does limit to you with *only* mysql >= 4.0 and
and is not a common thing in other dbms (iirc).  

The second option seems like a lot of work but, i can guarantee  you
that it will be much faster than selecting all rows in a resultset
and figururing out what to do, expecially on the later pages.


Here is some code that uses method 2, keep in mind that the does
several things, like generating the navigation (which makes it more
complex). I should probably seperate the pagination method a bit
more.

http://zirzow.dyndns.org/html/php/code/paginate.php

And an example usage of it:
http://www.bigstockphoto.com/search.php


HTH,

Curt
-- 
Quoth the Raven, "Nevermore."

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux