Re: Retrieving result of COUNT(*) with PHP

Pardon the label on the last run, it should be seconds.

On 3/28/07, Gavin M. Roy <[email protected]> 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: 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 <[email protected]> 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 <[email protected] >:
> > >
> > > 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 <[email protected] >:
> > >
> > > > 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: [email protected]
> >            [email protected]
> >            [email protected]
> >
> > Messenger: [email protected]
> >
> > Skype: valentinorfila
> >            [email protected]
> > ***************************************************************************
> >
>
>
>