Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Jun 11 '13 at 3:19

This question came from our site for professional and enthusiast programmers.

    
Show me your code. I dont need your query, just a way how are you testing it. –  Kasyx Jun 7 '13 at 12:31
3  
Yes, mySQL caches queries. It's clever like that. –  Spudley Jun 7 '13 at 12:32
    
@Kasyx what code? It's just basic PDO, but I don't think PHP can cache it because I run the PHP script twice, I don't run the query twice in one script. Also could you explain why you edited pdo in while it isn't really relevant to the question? –  Camil Staps Jun 7 '13 at 12:33
3  
All DBMSes have a page-level cache of some kind. Many go beyond that by caching query execution plans or even query results (including MySQL). I suspect this last thing is the main culprit for your observed behavior. –  Branko Dimitrijevic Jun 7 '13 at 12:35
    
@Spudley could you write an answer for it? :) –  Camil Staps Jun 7 '13 at 12:35

2 Answers 2

up vote 11 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
2  
"As of MySQL 5.1.17, the query cache is used for prepared statements under the conditions described in Section 8.6.3.1, “How the Query Cache Operates”. Before 5.1.17, the query cache is not used for prepared statements." dev.mysql.com/doc/refman/5.1/en/query-cache.html –  Zsolt Szilagy Jun 7 '13 at 12:46
    
"in common with all other popular database products": that is a bit misleading. Hardly any DBMS actively caches query results the way MySQL does. DBMS usually only cache table (or index) data, not query results. Most of them do cache the query exeution plan (and the query "source") –  a_horse_with_no_name Jun 10 '13 at 17:21
3  
"it can often use a cache for a query even if the exact parameters of the query are not the same" is completely incorrect. The query must be byte-for-byte identical to a previously-executed and still-cached query in order to be served from the cache. Even the difference between SELECT * and select * means an otherwise-identical query will not be served from the cache. dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html. Posted 5.1 link for consistency, but applies to all versions. –  Michael - sqlbot Jun 11 '13 at 3:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.