Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 86E2D9FBB50 for ; Wed, 28 Mar 2007 18:39:00 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 90941-01-2 for ; Wed, 28 Mar 2007 18:38:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.190]) by postgresql.org (Postfix) with ESMTP id 0A4D79FBA81 for ; Wed, 28 Mar 2007 18:38:55 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id x4so3811768nfb for ; Wed, 28 Mar 2007 14:38:55 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=QeP9Inmk0NPQ+p4jqINnmWIg1yhOFDh7yb0UvPmKdrRH/RxLiLv2XXktFntrzgT0P/SwqOTi9NY2+hORSabod01bcfUjCdl5zeKGhg0L2bNS7Fpo9LBSk2dFoxbsgIwxpj1NIX/4NijjIAByhVAd6/WIZv8MoXnfioWB15V/QgE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=PoAxwOFcVja3i2WfKLe8a6JoWPsLLg9Im5r0BdpF8bKeYWfqv3jiL5y5HVjb/hhbNYvErJVJDeXR0YG0mAEd1+OTb+HGwUNjtvb7ZYKekQoxq7zHfdUytpAFwjE/3OX0LQPmwrbfDJBKS68QUzHs6vOSGaybB0X9Qmec589xzPs= Received: by 10.82.154.2 with SMTP id b2mr3611631bue.1175117934769; Wed, 28 Mar 2007 14:38:54 -0700 (PDT) Received: by 10.82.114.5 with HTTP; Wed, 28 Mar 2007 14:38:46 -0700 (PDT) Message-ID: <5b599cc10703281438y18b0f663ib7e8f1e31aeca98@mail.gmail.com> Date: Wed, 28 Mar 2007 14:38:46 -0700 From: "Gavin M. Roy" To: "=?ISO-8859-1?Q?Valent=EDn_Orfila?=" Subject: Re: Retrieving result of COUNT(*) with PHP Cc: "Mihail Mihailov" , pgsql-php@postgresql.org In-Reply-To: <5b599cc10703281438h4b630e3dtdf60cfee265f6dc8@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_42597_26523161.1175117926536" References: <20070329000511.qidyijwozuo0w0w4@imp3.uta.fi> <6b8c0db0703281431m4b14237es333e1d6152f124de@mail.gmail.com> <5b599cc10703281438h4b630e3dtdf60cfee265f6dc8@mail.gmail.com> X-Google-Sender-Auth: 9fb784cc99b88723 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/60 X-Sequence-Number: 3772 ------=_Part_42597_26523161.1175117926536 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Pardon the label on the last run, it should be seconds. On 3/28/07, Gavin M. Roy wrote: > > For large volumes of data, this will be slower. PostgreSQL has to do mor= e > work on select * FROM table than select count(*) from table... > > I wrote a little test script to explain this: > > Run 1, with numRows: 60348 rows returned, duration of 0.379431 seconds > Run 1, with count: 60348 count returned, duration of 0.061221 seconds > > Run 2, with numRows: 60348 rows returned, duration of 0.323478 seconds > Run 2, with count: 60348 count returned, duration of 0.110275 seconds > > Run 3, with numRows: 60348 rows returned, duration of 0.379429 milisecond= s > Run 3, with count: 60348 count returned, duration of 0.061281 miliseconds > > Source Code: > > // pg_NumRows code > $start =3D microtime(true); > $result =3D pg_Exec($conn, "SELECT i_poemid FROM poetry;"); > $rows =3D pg_NumRows($result); > $end =3D microtime(true) - $start; > > // Count Code > $start =3D microtime(true); > $result =3D pg_Exec($conn, "SELECT count(*) FROM poetry;"); > $data =3D pg_Fetch_Object($result, 0); > $send =3D microtime(true) - $start; > > In this scenario, i_poemid is the primary key of a table with 60k rows. > > Regards, > > Gavin > > > > > On 3/28/07, Valent=EDn Orfila wrote: > > > > > > Where people this the way I do, count (*) could be slower > > > > > $sql =3D "select * from TableName"; > > $count =3D pg_query($sql); > > $count =3D pg_num_rows($count); > > ?> > > > > > > I thing that's enough :) > > > > 2007/3/28, Mihail Mihailov : > > > > > > Hi, > > > > > > actually, I don't think you need pg_fetch_all. > > > I use pg_fetch_row for the purpose. What you get with the SELECT > > > clause is one row. > > > > > > E.g. like this: > > > $res =3D pg_fetch_row(pg_query("SELECT COUNT(*) AS result_count FROM > > > etc. WHERE etc.")); //Not a very good style :-), one should check if > > > the query runs > > > $count =3D $res[0]; > > > > > > Another way to calculate number of rows in the result is to use > > > pg_num_rows function. > > > $res =3D pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE > > > etc."); > > > $count =3D pg_num_rows($res); > > > > > > Enjoy! > > > > > > Mihail > > > > > > > > > > > > Quoting Lynna Landstreet : > > > > > > > Hi there, > > > > > > > > I'm trying to use a SELECT COUNT(*) to count how many results would > > > be > > > > retrieved from a particular query (as part of the process of > > > paginating > > > > search results). > > > > > > > > But I'm having trouble figuring out how to retrieve the result of > > > the count > > > > in PHP. The result on its own is a resource rather than a specific > > > value, > > > > but when I try to retrieve the result via pg_fetch_result, some kin= d > > > of > > > > weird math error happens and I get a huge number that bears no > > > resemblance > > > > to the number of results the query actually gets when it runs > > > (1,714,608 for > > > > a query that in actuality produces three results). > > > > > > > > I tried using an AS clause (SELECT COUNT(*) AS result_count FROM > > > etc. WHERE > > > > etc.) to give the result a name, but that didn't help, and when I > > > tried > > > > using pg_fetch_all on the result to see exactly what it was > > > retrieving, I > > > > got this: > > > > > > > > Array > > > > ( > > > > [0] =3D> Array > > > > ( > > > > [result_count] =3D> 1714608 > > > > ) > > > > > > > > ) > > > > > > > > Again with the weird number. And yet, if I run the exact same query > > > in the > > > > SQL window of phpPgAdmin, I get the proper result count (3 in this > > > > instance). > > > > > > > > Does anyone know what's going on here? Can I just not use SELECT > > > COUNT(*) > > > > with PHP at all? > > > > > > > > I originally had the script running the actual query and then > > > counting the > > > > results, and then running it again with LIMIT and OFFSET to get one > > > page's > > > > worth of results, but it seemed wasteful to do it that way, so I wa= s > > > trying > > > > to do it more efficiently... :-/ > > > > > > > > Thanks, > > > > > > > > Lynna > > > > > > > > -- > > > > Spider Silk Design - http://www.spidersilk.net > > > > 509 St Clair W Box 73576, Toronto ON Canada M6C 1C0 > > > > Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 416.873.9289 > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > > TIP 2: Don't 'kill -9' the postmaster > > > > > > > > > > > > > ---------------------------(end of > > > broadcast)--------------------------- > > > TIP 9: In versions below 8.0, the planner will ignore your desire to > > > choose an index scan if your joining column's datatypes do not > > > match > > > > > > > > > > > -- > > ***********************************************************************= **** > > > > Ministerio de Planificaci=F3n y Desarrollo de la Rep=FAblica Bolivarian= a de > > Venezuela > > Direcci=F3n de Planes de Personal > > > > Jos=E9 Valent=EDn Orfila Brice=F1o > > Programador II > > > > Tel=E9fonos: Celular: (0416) 4131418 > > > > E-mail: valentinorfila@gmail.com > > valentinorfila@hotmail.com > > josevalentinorfila@yahoo.com > > > > Messenger: valentinorfila@hotmail.com > > > > Skype: valentinorfila > > valentinorfila@gmail.com > > ***********************************************************************= **** > > > > > ------=_Part_42597_26523161.1175117926536 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Pardon the label on the last run, it should be seconds.

On 3/28/07, Gavin M. Roy= <gmr@ehpg.net> wrote:=
For large volumes of data, this will be slower.  PostgreSQL has to do = more work on select * FROM table than select count(*) from table...

= I wrote a little test script to explain this:

Run 1, with numRows: 6= 0348 rows returned, duration of=20 0.379431 seconds
Run 1, with count: 60348 count returned, duration of 0.= 061221 seconds

Run 2, with numRows: 60348 rows returned, duration of= 0.323478 seconds
Run 2, with count: 60348 count returned, duration of= =20 0.110275 seconds

Run 3, with numRows: 60348 rows returned, duration = of 0.379429 miliseconds
Run 3, with count: 60348 count returned, duratio= n of 0.061281 miliseconds

Source Code:

  // pg_NumRows c= ode
  $start =3D microtime(true);
  $result =3D pg_Exec($conn,= "SELECT i_poemid FROM poetry;");
  $rows =3D pg_NumRows(= $result);
  $end =3D microtime(true) - $start;

  // Cou= nt Code
  $start =3D microtime(true);
  $result =3D pg_Exec($conn, "SELECT count(*) FROM poetry;&qu= ot;);
  $data =3D pg_Fetch_Object($result, 0);
  $send =3D microtime(true) - $start;

In this scenario,= i_poemid is the primary key of a table with 60k rows.

Regards,

Gavin





On 3/28/07, Valent=EDn Orfila &l= t; valentinorfila@gmail.com> wrote:

Where = people this the way I do, count (*) could be slower

<?
$sql = =3D "select * from TableName";
$count =3D pg_query($sql);   
$count =3D pg_num_rows= ($count);
?>
<td ><?=3D$count?></td>

I thing that's enough :)

2007/3/28, Mihail Mihailov <= Mihail.Mihailov@uta.fi >:
Hi,

actually, I don't think you need pg_fetch_all.
I use pg_f= etch_row for the purpose. What you get with the SELECT
clause is one row= .

E.g. like this:
$res =3D pg_fetch_row(pg_query("SELECT COU= NT(*) AS result_count FROM
etc. WHERE etc.")); //Not a very good style :-), one should check = if
the query runs
$count =3D $res[0];

Another way to calculate= number of rows in the result is to use
pg_num_rows function.
$res = =3D pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc.&quo= t;);
$count =3D pg_num_rows($res);

Enjoy!

Mihail


Quoting Lynna Landstreet <lynn= a@spidersilk.net >:

> Hi there,
>
> I'm trying to use a SEL= ECT COUNT(*) to count how many results would be
> retrieved from a particular query (as part of the process of pagin= ating
> search results).
>
> But I'm having trouble f= iguring out how to retrieve the result of the count
> in PHP. The res= ult on its own is a resource rather than a specific value,
> but when I try to retrieve the result via pg_fetch_result, some ki= nd of
> weird math error happens and I get a huge number that bears n= o resemblance
> to the number of results the query actually gets when= it runs (1,714,608 for
> a query that in actuality produces three results).
>
>= I tried using an AS clause (SELECT COUNT(*) AS result_count FROM etc. WHER= E
> etc.) to give the result a name, but that didn't help, and wh= en I tried
> using pg_fetch_all on the result to see exactly what it was retrie= ving, I
> got this:
>
> Array
>   &n= bsp; (
>         [0] =3D> = Array
>          &n= bsp; (
>         &n= bsp;   [result_count] =3D> 1714608
>           )
&= gt;
>     )
>
> Again with the weird = number. And yet, if I run the exact same query in the
> SQL window of= phpPgAdmin, I get the proper result count (3 in this
> instance).
>
> Does anyone know what's going on here? Can I just not = use SELECT COUNT(*)
> with PHP at all?
>
> I originally h= ad the script running the actual query and then counting the
> result= s, and then running it again with LIMIT and OFFSET to get one page's
> worth of results, but it seemed wasteful to do it that way, so I w= as trying
> to do it more efficiently... :-/
>
> Thanks,<= br>>
> Lynna
>
> --
> Spider Silk Design -=20 http://www.spidersilk.net
> 509 St Clair W Box 73576, Toronto ON = Canada M6C 1C0
> Tel 416.651.2899 - Toll Free 1.866.651.2899 - Cell 4= 16.873.9289
>
> ---------------------------(end of broadcast)--= -------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

<= br>---------------------------(end of broadcast)---------------------------=
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joini= ng column's datatypes do not
       ma= tch



--
***= ************************************************************************
Ministerio de Planificaci=F3n y Desarrollo de la Rep=FAblica Bolivarian= a de Venezuela
Direcci=F3n de Planes de Personal

Jos=E9 Valent= =EDn Orfila Brice=F1o
Programador II

Tel=E9fonos: Celular: (0416= ) 4131418

E-mail: valentinorfila@gmai= l.com
           <= a href=3D"mailto:valentinorfila@hotmail.com" target=3D"_blank" onclick=3D"r= eturn top.js.OpenExtLink(window,event,this)"> valentinorfila@hotmail.com
       = ;    josevalentinorfila@yahoo.com

Messenger: valentinorfila@hotmail.com

Skype: valentin= orfila
           valentinorfila@gmail.com
*******************************************************************= ********


------=_Part_42597_26523161.1175117926536--