Is there a better way to count query results?

[eluser]sandeep nami[/eluser]
@Micky
u can use it method is
just use this after the query '$this->db->count_all_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.

[eluser]mhulse[/eluser]
Hi Joshua! Thanks again for another very informative reply. Like I said before, I really appreciate you taking the time to share your pro knowledge and teach a noob a few new tricks.

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

Oh, not splitting hairs... I think it is good to clarify.

[quote author="Joshua Logsdon" date="1264449136"]This question isn't new so there are lots of takes:
http://www.mysqlperformanceblog.com/2007...-countcol/[/quote]

Great read! Thanks for sharing.

[quote author="Joshua Logsdon" date="1264449136"]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.

...<snip>...

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.[/quote]

Excellent advice and info!

I was planning on using all the advice you have given me. I have not had time to do any benchmarking, but I think the below query has been honed down to match all the great tips you have given me:

Code:

private function check_entry()
{
    $query = $this->db->select('1', FALSE)->where('container = ' . $this->container)->where('margin = ' . $this->margin)->limit(1)->get($this->table);
    return ($query->num_rows() > 0) ? TRUE : FALSE;
}

I have yet to test its speediness, but based on what we have talked about here (external links and all), it should be faster than my original count(*) approach.

Also, I like the thought of using PHP5 to chain, but it sure does kill the readability, and I just can't seem to figure out how to format it nicely with line breaks and tabs! Hmm, maybe there is something in the CI style guide.

Thanks again Joshua! I have learned a lot based on your help here.

Have a great day,

Cheers,
Micky