|
|
Posted by M. Sokolewicz on 01/08/05 02:09
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
Navigation:
[Reply to this message]
|