DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding;
	b=R1jqhsoVH7tg3d6kJwq/cNYG3rssCFy4ialixm+vfvQlI3ZIuQD6aYf7cmS4z+7psSF5LJBiGYInBQdd6uRi/r7DqQMoqPQCMbDPbCBReeQ5JKh+r9xvECZD1Hw+QglA87KbhvmqhwZlyKDs/j6RFgbMO+wAPgPUFMK9r9hSsWE=
Message-ID: <457CF427.30507@gmail.com>
Date: Mon, 11 Dec 2006 17:01:11 +1100
From: Chris <dmagick@gmail.com>
User-Agent: Thunderbird 1.5.0.8 (Windows/20061025)
MIME-Version: 1.0
To: Mark Kirkwood <markir@paradise.net.nz>
CC: pgsql-performance@postgresql.org
Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
References: <457814D3.1030001@mbsi.com.br> <457CD182.7020004@gmail.com>
	<1165809878.19970.2.camel@localhost.localdomain>
	<457CE03C.1060707@gmail.com> <457CF184.6020702@paradise.net.nz>
In-Reply-To: <457CF184.6020702@paradise.net.nz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Mark Kirkwood wrote:
> Chris wrote:
> 
>> It's the same as doing a select count(*) type query using the same 
>> clauses, but all in one query instead of two.
>>
>> It doesn't return any extra rows on top of the limit query so it's 
>> better than using pg_numrows which runs the whole query and returns it 
>> to php (in this example).
>>
>>
>> Their docs explain it:
>>
>> http://dev.mysql.com/doc/refman/4.1/en/information-functions.html
>>
>> See "FOUND_ROWS()"
>>
> 
> Note that from the same page:
> 
> "If you are using SELECT SQL_CALC_FOUND_ROWS, MySQL must calculate how 
> many rows are in the full result set. However, this is faster than 
> running the query again without LIMIT, because the result set need not 
> be sent to the client."
> 
> So it is not as cost-free as it would seem - the CALC step is 
> essentially doing "SELECT count(*) FROM (your-query)" in addition to 
> your-query-with-the-limit.
> 
> I don't buy the "its cheap 'cause nothing is returned to the client" 
> bit, because 'SELECT count(*) ...' returns only 1 tuple of 1 element to 
> the client anyway. On the face of it, it *looks* like you save an extra 
> set of parse, execute, construct (trivially small) resultset calls - but 
> 'SELECT FOUND_ROWS()' involves that set of steps too, so I'm not 
> entirely convinced that doing a 2nd 'SELECT count(*)...' is really any 
> different in impact.

Sorry - I created a bit of confusion here. It's not doing the count(*), 
it's doing the query again without the limit.

ie:

select SQL_CALC_FOUND_ROWS userid, username, password from users limit 10;

will do:

select userid, username, password from users limit 10;

and calculate this:

select userid, username, password from users;

and tell you how many rows that will return (so you can call 
'found_rows()').


the second one does do a lot more because it has to send the results 
across to the client program - whether the client uses that info or not 
doesn't matter.


The OP didn't want to have to change to using two different queries:
select count(*) from table;
select * from table limit 10 offset 0;


Josh's comment was to do the query again without the limit:
select userid, username, password from users;

and then use something like http://www.php.net/pg_numrows to work out 
the number of results the query would have returned.. but that would 
keep the dataset in memory and eventually with a large enough dataset 
cause a problem.

-- 
Postgresql & php tutorials
http://www.designmagick.com/