Posted by Yangshiqi on 01/19/05 07:03

Yes. I just use the same method which Curt mentioned below and seem well.
But I met the problem a little bit complex that the sql used "group by".
Any idea?


Best regards,
Yang Shiqi




-----Original Message-----
From: Curt Zirzow [mailto:php-general@zirzow.dyndns.org]
Sent: Sunday, January 09, 2005 2:37 AM
To: php-general@lists.php.net
Subject: Re: [PHP] Re: Pagination Optimization

* 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

[Back to original message]