Is there a better way to count query results?

[eluser]Joshua Logsdon[/eluser]
Hi guys, I promise this will be the last info from me because I know I'm "splitting hairs" at this point!

This question isn't new so there are lots of takes:
http://www.mysqlperformanceblog.com/2007...-countcol/

In Micky's code he wants to know if a record exists, not necessarily how many records there are because in theory his code should only be allowing one anyway. He's also looking for "better" ways to do it and is eager to learn so I'm trying to put the "why" behind answers so he can better understand and decide for himself.

1) Yes you can do this via COUNT(*). MyISAM caches the number of rows in the table so $this->db->count_all_results() would be the fastest. THAT'S IF you wanted how many total rows are in the table, i.e. no WHERE clause.

2) Because there is a WHERE clause, I believe MySQL has to perform a standard query then the aggregate COUNT() function. Because he is using a compound key, it will still be fast. A row will always be returned with the count.

3) With the SELECT statement, the standard query is still run but no aggregate function is run. Also we are trying to limit to 1 match, so one row will be returned IF one was found.

4) If you try benchmarking any of these queries (http://dev.mysql.com/doc/refman/5.0/en/i...tions.html) you are going to find with a small result set that 2 & 3 are basically the same. If you have a huge result set, you should notice 3 start to be faster.

You can't lose here Micky; what you were doing from the beginning would have kept on working for you. Just think of this as an exercise in SQL and then do what you feel is best or makes sense to you.