Mysql – Does MySQL cache queries


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.