Hello Richard, Wednesday, July 6, 2005, 10:18:05 PM, you wrote: RL> Actually, I think the "SELECT id" will transfer less data in the RL> cases where no rows are returned. Yes, I would agree with that. RL> There are no rows to return, after all, whereas the count(*) will RL> always return exactly one row. Yup. RL> And in the case case where a row is returned, the id will probably RL> be the same number of bytes as a count(*): a 32-bit integer. Say you've got user number 20,000 in a table. He only exists once, so count() only returns 1. Bring back the ID and you'll get sent a value of 20000. I would be shocked if, on a byte for byte transfer level, there was no difference between the two. Once it's in memory at the other end, sure they'll both most likely become longs anyway. As for the query itself, MySQL could use its query cache on either and both would require the same analysis of the index/table to obtain their end result due to the where clause. RL> What's more, the num_rows is always available, so is PROBABLY RL> pushed through the MySQL/PHP pipe from the get-go. So count(*) has RL> the 32-bits for the number of rows, which is always 1, and 32-bits RL> for the result, which is either 1 or 0. Yes, the num_rows value will always be available, because of the success of the select query. So yes, it is passed back and ultimately stored in a long. Using count() has no effect here. RL> But I'm guessing that if you actually managed to measure it RL> accurately, "SELECT id" would be a gnat's whisker "faster" in RL> cases where no rows were returned, and barely perceptible "faster" RL> when there is a row, since MySQL doesn't have to "count()" the RL> result set -- Which really means just copying the num_rows it has RL> already calculated, most likely, in place of the actual result. >From MySQLs point of view num_rows is calculated and returned to PHP irrespective of which method you use. You could mysql_num_rows it and then PHP will have to perform a zend fetch resource, doing an index look-up on the hash (and returning the result). Or if you've count'ed it, it'll have to return the value stored via a mysql_result call (or similar), which takes a few more trips around the hash table. When it comes down to milliseconds, I doubt there is much in it to be honest. Certainly something hard (and I'm sure you'd agree useless) to quantify. But hey, we're being pedantic here, yes? :) At the end of the day it just seems like a coding preference to me. After all, I don't want the users ID number, I want to know how many users exist matching that username (i.e. I want a count of them), so that's what I've directly asked MySQL for. A "one cat, 1 billion skinning combinations" scenario again I feel. Best regards, Richard Davey -- http://www.launchcode.co.uk - PHP Development Services "I do not fear computers. I fear the lack of them." - Isaac Asimov -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
