Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can

From: Chris <dmagick(at)gmail(dot)com>
To: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
Cc: marcosborges(at)mbsi(dot)com(dot)br, pgsql-performance(at)postgresql(dot)org
Subject: Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
Date: 2006-12-11 04:36:12
Message-ID: 457CE03C.1060707@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Joshua D. Drake wrote:
> On Mon, 2006-12-11 at 14:33 +1100, Chris wrote:
>> Marcos Borges wrote:
>>> 07/12/2006 04:31
>>> *SQL_CALC_FOUND_ROWS in POSTGRESQL*
>>>
>>> In mysqln i m using the command SQL_CALC_FOUND_ROWS in follow sintax.
>>> SELECT SQL_CALC_FOUND_ROWS name, email, tel FROM mytable WHERE name <>
>>> '' LIMIT 0, 10
>>> to have the recorset data.
>>> and
>>> SELECT FOUND_ROWS();
>>> to have the total of registers found.
>>>
>>> I dont want to use the command count(*), because the performance will
>>> fall down, depending of the quantyt of tables and "joins".
>>>
>>> The Data base postgresql have something similar ???
>> Nope, you're out of luck sorry. That's a mysql-ism and I doubt postgres
>> will ever include something similar.
>
> Your language will have a similar binding. Something like pg_numrows.

I guess they are similar but also not really :)

The SQL_CALC_FOUND_ROWS directive in mysql will run the same query but
without the limit.

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()"

--
Postgresql & php tutorials
http://www.designmagick.com/

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2006-12-11 04:57:18 Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can
Previous Message Joshua D. Drake 2006-12-11 04:04:38 Re: SQL_CALC_FOUND_ROWS in POSTGRESQL / Some one can