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 = "" 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@xxxxxxxxx> 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@xxxxxx >: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@xxxxxxxxxxxxxx >:
> 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@xxxxxxxxx
valentinorfila@xxxxxxxxxxx
josevalentinorfila@xxxxxxxxx
Messenger: valentinorfila@xxxxxxxxxxx
Skype: valentinorfila
valentinorfila@xxxxxxxxx
***************************************************************************
