From: Chris <dmagick@gmail.com>
To: 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 17:01:11 +1100
Message-ID: <457CF427.30507@gmail.com> (raw)
In-Reply-To: <457CF184.6020702@paradise.net.nz>
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>
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/
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: dmagick@gmail.com, markir@paradise.net.nz
Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
In-Reply-To: <457CF427.30507@gmail.com>
* 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