At 10/26/2006 10:38 AM, afan@xxxxxxxx wrote:
what would be better solution to count records in table (e.g., how many
customers have last name 'Smith'):
$query = mysql_query("
SELECT COUNT(*) as NoOfRecords
FROM customers
WHERE last_name = 'Smith'");
$result = mysql_fetch_array($query);
$NoOfRecords = $result['NoOfRecords'];
OR
$query = mysql_query("
SELECT cust_id
FROM customers
WHERE last_name = 'Smith'");
$NoOfRecords = mysql_num_rows($query);
My understanding of why COUNT() is the better solution is that
mysql_num_rows() requires MySQL to cycle through the found records on
a second pass to count them, whereas the first gathers the count
during the first pass, the record-selection phase.
Of course, if you also need to have the selected records accessible
to a loop, using COUNT() will force you to execute two queries, one
for the record count and one for the data themselves, so at that
point the relative advantage is less clear.
While we're talking about optimization, I'd want to check to make
sure COUNT(*) didn't ask MySQL to generate a throw-away recordset
consisting of all fields. I wonder if it would be more
machine-efficient to use COUNT(`last_name`), specifying a single
field, in this case the same field your query is examining anyway.
Regards,
Paul
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
