Posted by Curt Zirzow on 01/08/05 20:37

* 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."

[Back to original message]