20

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.

Peter Mortensen
  • 349
  • 2
  • 10

2 Answers2

15

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.

Masoud R
  • 17
  • 6
Branko Dimitrijevic
  • 690
  • 5
  • 12
6

Yes, mySQL (in common with all other popular database products) caches the queries that are made to it.

The caching is pretty clever -- it can often use a cache for a query even if the exact parameters of the query are not the same. This can make a big difference to performance.

Caching is controlled entirely inside the DB server software; you do not have any visibility of what the cache contains, nor how long a given item remains in the cache; it could be overwritten at any given moment depending on what other queries are being called, etc. It is there to aid performance, but it should not be relied on for performance.

You can read more about it here in the MySQL manual

In addition, using PDO allows you to write your queries as "Prepared statements", binding the parameters rather than hard coding them into a plain text query string. This also has caching implication on the DB server and for queries that are repeated, will also improve performance.

Masoud R
  • 17
  • 6