Re: Re: Pagination Optimization

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

-- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php



[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux