From: Mark Kirkwood <markir@paradise.net.nz>
To: Chris <dmagick@gmail.com>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: marcosborges@mbsi.com.br
Cc: pgsql-performance@postgresql.org
Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
Date: Mon, 11 Dec 2006 18:49:56 +1300
Message-ID: <457CF184.6020702@paradise.net.nz> (raw)
In-Reply-To: <457CE03C.1060707@gmail.com>
References: <457814D3.1030001@mbsi.com.br>
<457CD182.7020004@gmail.com>
<1165809878.19970.2.camel@localhost.localdomain>
<457CE03C.1060707@gmail.com>
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
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: markir@paradise.net.nz, dmagick@gmail.com, jd@commandprompt.com, marcosborges@mbsi.com.br
Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
In-Reply-To: <457CF184.6020702@paradise.net.nz>
* 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