Why is one one of these DB query methods (using a where filter) faster than the other (a direct get_var() query)? [closed]

Question

In this thread I presented what I thought was a good answer, and @toscho presented one afterwards which to me seemed inferior.

But after running some timed tests, my code turns out to be significantly slower. My code takes >3 times longer to execute, and I can't understand why. Hopefully a WordPress guru can enlighten me.

The OP in that thread only wanted the number of top-level comments, ignoring any nested ones. toscho's function returns the entire rowset of matches, while mine only returns the number of matches.

Here's my code:

function get_num_toplevel_comments() {
    global $wpdb;

    return $wpdb->get_var("
        SELECT COUNT(*) 
        FROM $wpdb->comments
        WHERE comment_parent = 0
    ");
}

And here's toscho's

add_filter( 'comments_clauses', 'wpse_78628_top_comments_only' );
$comments = get_comments();
remove_filter( 'comments_clauses', 'wpse_78628_top_comments_only' );
function wpse_78628_top_comments_only( $clauses )
{
    $clauses['where'] .= ' AND comment_parent = 0';
    return $clauses;
}

P.S. - I also included in my testing SELECT COUNT(comment_ID) instead of *. Both had very similar response times, negligible in fact since my tests are greatly affected by my shared hosting speed. That was also a surprise to me.

I'm far from an expert in MySQL, WordPress, or PHP. But I tend to get anal about any perceived efficiency in my code (and to a lesser extent, others' code) and always strive for efficient solutions.

Answer 1

The two functions do very different things. Yours only gets a count while toscho's pulls an array of comment data, some of which require joins on other tables-- the 'author' stuff, for example. I am sure that is where the difference in speed is.

I would lean towards toscho's code because it should be more stable long term-- that is, as core WordPress code changes-- unless this were a site I personally maintain :)

Looks like you can pass get_comments a count parameter though and it should work very much like your.