Posted by Bruno B B Magalh�es on 01/08/05 16:43

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);
$total_rows = $this->num_rows();

if($total_rows > $itens)
{
$this->total_pages = ceil($total_rows/$itens);
$this->pages_before = ceil($page - 1);
$this->pages_after = ceil($this->total_pages - $page);

$this->query($query.' LIMIT '.(($page*$itens)-$itens).','.$itens);

while($this->fetch_array())
{
$results[] = $this->row;
}
}
elseif($total_rows > 0)
{
while($this->fetch_array())
{
$results[] = $this->row;
}

$this->total_pages = '1';
$this->pages_before = '0';
$this->pages_after = '0';
}
else
{
return null;
}

return $results;
}
=======================================


Regards,
Bruno B B Magalhães

On Jan 7, 2005, at 10:09 PM, M. Sokolewicz wrote:
> first of all, you're running 4 queries here. 4 queries is a lot!
> Especially when you don't need more than 2 ;)
> the problem here is that your queries are pretty "unknown" to this
> function. Although it does a nice result for that unknowing, there's a
> few minor things that make it faster.
>
> First of all, would be using less queries.
> What I usually do is issue a query like this:
> "SELECT count(some_unique_col) WHERE
> (that_where_clause_youre_using_in_the_select_query)"
> then, we do some math.
>
> $pages_before = $page-1;
> $rows_before = $pages_before*$itens;
>
> $rows_after = $total_number_of_rows-($page*$itens);
> $pages_after = ceil($rows_after/20);
>
> Then do the actual selecting of the rows using the limit.
>
> The thing that makes it slow in your example is the fact that 4 times
> you're selecting ALL data from the relevant rows, and buffer it. You
> buffer it, but don't use any of it, except for the number of rows.
> Mysql does a far quicker job at this than PHP would, so use mysql. :)
> Then, you're using 3 queries to determine the rows around the page;
> even though, with a bit of simple math, you can calculate it. And
> trust me on this, simple math is faster ;)
>
> anyway, hope that helped.

[Back to original message]