Correct way to perform non-cacheable DB query

Question

I am not completely sure what you are talking about so I will mention the possibilities that come to mind.

Maybe this…

// Keep track of the last query for debug..
$this->last_query = $query;
http://core.trac.wordpress.org/browser/tags/3.5.2/wp-includes/wp-db.php#L1195

… is what you are talking about. If so, your code just runs the flush method which resets $wpdb properties to null or empty values. It doesn’t “prevent” anything and doesn’t really look like “caching” either. It just adds a little bit more work for PHP to do.

Or maybe you are talking about this:

$this->result = @mysql_query( $query, $this->dbh );
http://core.trac.wordpress.org/browser/tags/3.5.2/wp-includes/wp-db.php#L1200

But I don’t know how that counts as “caching”. The class has to retrieve results and put them somewhere. That somewhere is $this->results;

Or maybe you are talking about this:

The get_var function returns a single variable from the database.
Though only one variable is returned, the entire result of the query
is cached for later use. Returns NULL if no result is found.

http://codex.wordpress.org/Class_Reference/wpdb#SELECT_a_Variable

If you tried $wpdb->get_var("SELECT ID FROM {$wpdb->posts}"); you’d get a result set equal to the rows in your database but get_var would only return one result. The other results are still held in the object. If you happen to need those results (slightly) later, they are available without another query.

var_dump($wpdb->get_var("SELECT ID FROM {$wpdb->posts}"));
var_dump($wpdb->last_result);

This is the only thing that really counts as “caching” to my mind, though I have never needed it. It makes no real difference with your query since that query will only return a single row anyway.

Are there any better way to write the above function?

Leave out $wpdb->flush() as I really don’t see the benefits, for any of those cases mentioned, especially with a query like "SELECT NOW()". As soon as the next query runs $wpdb->flush will run anyway.

And there is no reason to set the $now variable and then return it. Unless you intend to add sanity checks on the $now data before returning it, ust return it straight from the query.

Why $wpdb ONLY cache the last result but not all the queries’ result
in the same request? Sound like a little bit overkill for just the
last query, right?

Despite the Codex’s use of the term “cache” I am not sure most of this counts as a “cache” but I won’t argue the point. It is a very minimal cache if it is one at all. The “cache” is mostly an artifact of how $wpdb works.

As far as “caching” every query in a request, I’d expect memory issues if you tried that– with some servers at least. Imagine 4 or 5 very large result sets all taking up memory.

$wpdb is really just a relatively simple interface to the database. It is convenient but I expect you may be treating it like or expecting it to be more than it is. It runs a query, saves a little bit of information for its own use and for debugging, and when another query runs, other than the basic connection information and some other basic things like table name shortcuts ($wpdb->posts), it gets wiped clean and starts over. It is not a global query manager or query cache.

Based on the updated description, I ran:

function getNow() {
    global $wpdb;
    $now = $wpdb->get_var("SELECT NOW()");
    $wpdb->flush();
    return $now;
}

echo '<pre>';
echo getNow();
echo "\n";
sleep(10);
echo getNow(); // Same result as above
echo '</pre>';
die;

And got:

2013-06-25 10:37:06
2013-06-25 10:37:16

Every time I run that the two results differ by ten seconds.

Then I tried it with the $wpdb->flush(); commented out. Same results. That makes sense because $wpdb->flush(); runs before every query anyway as you can see from the source.

The effect you are seeing is not the result of $wpdb‘s internal operation. There is some other caching mechanism in play.

Related Posts: