Mysql – Does MySQL cache queries

MySQL

I'm interfacing a MySQL database with PHP Data Objects (PDO) and executing an extensive SQL query. Normally, it takes about 1500 ms; I still need to optimize it. When I run the PHP script twice with a short interval in between, the query only takes about 90 ms. The query is in both cases the same. When I run the script, with the same query, after some time again, it takes 1500 ms again.

Why is that? Does the database cache automatically? Is there some time the database saves the cache and then automatically deletes it?

I assume the results can't be cached by PHP, because this happens in two different threads. I wouldn't think PHP would cache the results, because it can't know if the database has changed.

I have a script running every minute to insert new rows to the database. This might also be the reason that it takes 1500 ms again after some time; the cache would've been deleted, because the relevant tables aren't the same anymore.

Best Answer

This is likely an artifact of the MySQL Query Cache.

You execute the SQL query, MySQL caches its result and the next execution if fast. When you run the script to insert the data in tables referenced by your query, the result cache gets invalidated and the query must be executed "for real" the next time.

From the MySQL documentation linked above:

A query mix consisting almost entirely of a fixed set of SELECT statements is much more likely to benefit from enabling the cache than a mix in which frequent INSERT statements cause continual invalidation of results in the cache.

Related Solutions

Mysql – Memcache implementation

You should decide what exactly you need, before deciding those 3 options.

My suggestion is as following:

Install memcache and the necesary php module to use with memcache. Once you install memcache, you will have an IP and PORT to connect to, for SET / GET operations
Decide where exactly in your Wordpress php code you need to inject the code that displays the "featured" stuff
Add a php code similar to:
- CONNECT to memcache
- Check for the existance of a KEY/VALUE pair, named "FEATURED STUFF"
- If there is, GET that pair, and display the pair's value
- If not, run the SELECT query, FETCH the results, build a string that will contain the HTML body of "FEATURED STUFF", PUT the string in memcache as a KEY/VALUE pair.
The code above should not be cached with WP Super Cache. Let memcache do the cacheing for you.

If you need help with the actual implementation, let me know.

Sample PHP Code:

$memcache = new Memcache;
$memcache->connect('localhost', 11211) or die ("Could not connect");

if ($gotten = $memcache->get('featured_stuff')) {
    echo 'CACHE HIT'</br>';
    echo $gotten;
} else {
    # Never mind - fetch it and store for next time!
    $gotten = dbfetch($query, ...);
    $memcache->set($featured_stuff,$gotten);
    echo $gotten;
}

Mysql – Same Parameter in MySQL stored procedure

First of all, I don't think it's a good idea to use dynamic sql in the case when you have nothing really 'dynamic'.

If you still want to do it, it seems that you are missing an important point about prepared statements. You are preparing, executing, and then deallocating prepared statement in one procedure; thus, next time you call your procedure, you are repeating all the steps again. Take a look on https://stackoverflow.com/questions/11605783/mysql-permanently-prepared-statements , there is an example on how to get benefits from prepared statements inside stored procedure (briefly, you do not deallocate statement at the end, instead of this, you check whether a variable associated with the statement is not null and call prepare only once. That expands lifetime of the prepared statement to lifetime of session effectively caching execution plan).

Best Answer

Related Solutions

Mysql – Memcache implementation

Mysql – Same Parameter in MySQL stored procedure

Related Question