On Thu, October 26, 2006 12:57 pm, Paul Novitski wrote:
>>$query = mysql_query("
>> SELECT COUNT(*) as NoOfRecords
>> FROM customers
>> WHERE last_name = 'Smith'");
>>$result = mysql_fetch_array($query);
>>$NoOfRecords = $result['NoOfRecords'];
This would be faster.
Index on last_name will probably help a great deal.
>>$query = mysql_query("
>> SELECT cust_id
>> FROM customers
>> WHERE last_name = 'Smith'");
>>$NoOfRecords = mysql_num_rows($query);
This will be slower as MySQL/PHP have to store and maintain a dataset
of all 'Smith' records.
> 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.
That too.
> 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.
You'd have to test on your hardware and your dataset to see which is
faster at that point, I think.
And if the dataset is small enough to get the whole shebang, then you
probably don't care.
And if it's not, then you want LIMIT/OFFSET and then you want
SQL_CALC_FOUND_ROWS or whatever it is with the mysql_num_rows() so you
know how many rows there were without the LIMIT.
> 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.
count(*) is heavily optimized in MySQL, as I understand it.
All this belongs on a MySQL list, not here, anyway.
--
Some people have a "gift" link here.
Know what I want?
I want you to buy a CD from some starving artist.
http://cdbaby.com/browse/from/lynch
Yeah, I get a buck. So?
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
