Re: counting records in db

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?