pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gavin M. Roy <gmr@ehpg.net>
To: Valentín Orfila <valentinorfila@gmail.com>
Cc: Mihail Mihailov <Mihail.Mihailov@uta.fi>
Cc: pgsql-php@postgresql.org
Subject: Re: Retrieving result of COUNT(*) with PHP
Date: Wed, 28 Mar 2007 14:38:23 -0700
Message-ID: <5b599cc10703281438h4b630e3dtdf60cfee265f6dc8@mail.gmail.com> (raw)
In-Reply-To: <6b8c0db0703281431m4b14237es333e1d6152f124de@mail.gmail.com>
References: <C2303737.4AFAD%lynna@spidersilk.net>
	<20070329000511.qidyijwozuo0w0w4@imp3.uta.fi>
	<6b8c0db0703281431m4b14237es333e1d6152f124de@mail.gmail.com>

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: 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 miliseconds
Run 3, with count: 60348 count returned, duration of 0.061281 miliseconds

Source Code:

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

  // Count Code
  $start = microtime(true);
  $result = pg_Exec($conn, "SELECT count(*) FROM poetry;");
  $data = pg_Fetch_Object($result, 0);
  $send = 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ín Orfila <valentinorfila@gmail.com> wrote:
>
>
> Where people this the way I do, count (*) could be slower
>
> <?
> $sql = "select * from TableName";
> $count = pg_query($sql);
> $count = pg_num_rows($count);
> ?>
> <td ><?=$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_fetch_row for the purpose. What you get with the SELECT
> > clause is one row.
> >
> > E.g. like this:
> > $res = 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 = $res[0];
> >
> > Another way to calculate number of rows in the result is to use
> > pg_num_rows function.
> > $res = pg_query("SELECT COUNT(*) AS result_count FROM etc. WHERE etc.");
> >
> > $count = pg_num_rows($res);
> >
> > Enjoy!
> >
> > Mihail
> >
> >
> >
> > Quoting Lynna Landstreet <lynna@spidersilk.net>:
> >
> > > 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 kind
> > 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] => Array
> > >            (
> > >             [result_count] => 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 was
> > 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ón y Desarrollo de la República Bolivariana de
> Venezuela
> Dirección de Planes de Personal
>
> José Valentín Orfila Briceño
> Programador II
>
> Teléfonos: Celular: (0416) 4131418
>
> E-mail: valentinorfila@gmail.com
>            valentinorfila@hotmail.com
>            josevalentinorfila@yahoo.com
>
> Messenger: valentinorfila@hotmail.com
>
> Skype: valentinorfila
>            valentinorfila@gmail.com
> ***************************************************************************
>

view thread (15+ messages)  latest in thread

Message-ID: <5b599cc10703281438h4b630e3dtdf60cfee265f6dc8@mail.gmail.com>
Permalink:  ../5b599cc10703281438h4b630e3dtdf60cfee265f6dc8@mail.gmail.com/
Also on:    postgresql.org/message-id/5b599cc10703281438h4b630e3dtdf60cfee265f6dc8@mail.gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: gmr@ehpg.net, valentinorfila@gmail.com, Mihail.Mihailov@uta.fi
  Subject: Re: Retrieving result of COUNT(*) with PHP
  In-Reply-To: <5b599cc10703281438h4b630e3dtdf60cfee265f6dc8@mail.gmail.com>
  References: <C2303737.4AFAD%lynna@spidersilk.net> <20070329000511.qidyijwozuo0w0w4@imp3.uta.fi> <6b8c0db0703281431m4b14237es333e1d6152f124de@mail.gmail.com> <5b599cc10703281438h4b630e3dtdf60cfee265f6dc8@mail.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