Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
From: Ragnar <gnari@hive.is>
To: Chris <dmagick@gmail.com>
Cc: Mark Kirkwood <markir@paradise.net.nz>, pgsql-performance@postgresql.org
In-Reply-To: <457CF427.30507@gmail.com>
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>
	<457CF427.30507@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Date: Mon, 11 Dec 2006 09:39:32 +0000
Message-Id: <1165829972.6369.26.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

On mán, 2006-12-11 at 17:01 +1100, Chris wrote:
> 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."

yes but not any faster than a 
select count(*) from (full query without LIMIT)

so the only advantage to the SQL_CALC_FOUND_ROWS thingie
is that instead of doing
   select count(*) from full-query
   select * from query-with-LIMIT
which will do the query twice, but possibly with
different optimisations,

you would do a non-standard
   select SQL_CALC_FOUND_ROWS query-with-LIMIT
   select FOUND_ROWS()
which will do one full query, without any
LIMIT optimisation, but with the same
number of round-trips, and same amount of
data over the line.

the only case where the second way may be
more effective, is if no LIMIT optimisation
can be made, and where the dataset is larger
than file buffer space, so that there is no
effect from caching.

gnari