Re: counting records in db

At 10/26/2006 10:38 AM, afan@afan.net 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