From: Ragnar <gnari@hive.is>
To: Chris <dmagick@gmail.com>
Cc: Mark Kirkwood <markir@paradise.net.nz>
Cc: pgsql-performance@postgresql.org
Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
Date: Mon, 11 Dec 2006 09:39:32 +0000
Message-ID: <1165829972.6369.26.camel@localhost.localdomain> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gnari@hive.is, dmagick@gmail.com, markir@paradise.net.nz
Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
In-Reply-To: <1165829972.6369.26.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox