Re: Re: Pagination Optimization

and now a few small comments to your code ;) Bruno B B Magalhães wrote:

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);  // No need to ceil this!
// $page is an integer, meaning a full numer (like 1, 10, 125, -15, etc.) ceiling the result is not useful, since the result is an integer aswell.

            $this->pages_after = ceil($this->total_pages - $page); // same thing here

// no need to ceil it.

                       $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';
// here, I suggest not making those values STRINGS, but instead leaving them be integers. So instead do:

/*

             $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.