Re: Pagination Optimization

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. Bruno B B Magalhães wrote:

Hi guys, currently I have a function in my framework´s mysql driver , that fetch paginated results... Here it´s: ===============================================================

    /*****************************************************
    *  Fetch paginated results
    *****************************************************/
    function fetch_paginated($query='',$page=1,$itens=20)
    {
        $this->query($query.' LIMIT '.(($page*$itens)-$itens).','.$itens);

        if($this->num_rows() > 0)
        {
            while($this->fetch_array())
            {
                $results[] = $this->row;
            }
        }
        else
        {
            return null;
        }

        $this->query($query.' LIMIT 0,'.(($page*$itens)-$itens));
        $this->pages_before = ceil($this->num_rows()/$itens);

        $this->query($query.' LIMIT

'.($page*$itens).',1000000000000000000');

        $this->pages_after = ceil($this->num_rows()/$itens);

        $this->query($query);
        $this->total_pages = ceil($this->num_rows()/$itens);

        return $results;
    }

=============================================================== My question is: Is there ANY way to speed up this function, or any way to fetch paginated results quicker? I had a project list, without pagination, and when I added the pagination function, it slowed down up to 0.0125 secs. Before it was running at 0.0600 more or less, and now it´s running at 0.07 to 0.075... Best Regards, Bruno B B Magalhães