Date: Mon, 11 Dec 2006 18:49:56 +1300
From: Mark Kirkwood <markir@paradise.net.nz>
Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
In-reply-to: <457CE03C.1060707@gmail.com>
To: Chris <dmagick@gmail.com>
Cc: "Joshua D. Drake" <jd@commandprompt.com>, marcosborges@mbsi.com.br,
	pgsql-performance@postgresql.org
Message-id: <457CF184.6020702@paradise.net.nz>
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <457814D3.1030001@mbsi.com.br> <457CD182.7020004@gmail.com>
	<1165809878.19970.2.camel@localhost.localdomain>
	<457CE03C.1060707@gmail.com>
User-Agent: Thunderbird 1.5.0.8 (X11/20061129)

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.

Cheers

Mark