MySQL Query Cache

MySQL has a great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know.

First let me clarify what MySQL Query Cache is – I’ve seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache – meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but full result sets. This means it is much more efficient as query which required processing millions of rows now can be instantly summoned from query cache. It also means query has to be exactly the same and deterministic, so hit rate would generally be less. In any case it is completely different.

Query cache is great for certain applications, typically simple applications deployed on limited scale or applications dealing with small data sets. For example I’m using Query Cache on server which runs this blog. Updates are rather rare so per-table granularity is not the problem, I have only one server and number of queries is small so cache duplication is not the problem. Finally I do not want to hack wordpress to support eaccelerator cache or memcached. Well honestly speaking if performance would be problem I should have started with full page caching rather than MySQL level caching but it is other story.

Lets talk a bit about features and limitations of Query Cache:

Transparent Caching – Caching is fully transparent to the application, and what is very important it does not change semantics of the queries – you always get actual query results. Really there are some chevats – if you’re not using query_cache_wlock_invalidate=ON locking table for write would not invalidate query cache so you can get results even
if table is locked and is being prepared to be updated. So if you’re using query cache in default configuration you can’t assume locking table for write will mean no one will be able to read it – results still can come from query cache unless you enable query_cache_wlock_invalidate=ON.

Caching full queries only – Meaning it does not work for subselects, inline views, parts of the UNION. This is also common missunderstanding.

Works on packet level – This is one of the reason for previous item. Query cache catches network packets as they sent from client to the server, which means it can serve responses very fast doing no extra conversion or processing.

Works before parsing – One more reason for high performance is Query Cache performs query lookup in the cache before query parsing, so if result is served from query cache, query parsing step is saved.

Queries must be absolutely the same As no parsing is done before lookup queries are not normalized (would require parsing) before cache lookup, so they have to match byte by byte for cache hit to happen. This means if you would place dynamic comments in the query, have extra space or use different case – these would be different queries for query cache.

Only SELECT queries are cached SHOW commands or stored procedure calls are not, even if stored procedure would simply preform select to retrieve data from table.

Avoid comment (and space) in the start of the query – Query Cache does simple optimization to check if query can be cached. As I mentioned only SELECT queries are cached – so it looks at first letter of the query and if it is “S” it proceeds with query lookup in cache if not – skips it.

Does not support prepared statements and cursors Query Cache works with query text and want full result set at once. In prepared statements there is query with placeholders and additional parameter values which would need extra care – it is not implemented. Cursors get data in chunks so it is even harder to implement.

Might not work with transactions – Different transactions may see different states of the database, depending on the updates they have performed and even depending on snapshot they are working on. If you’re using statements outside of transaction you have best chance for them to be cached.

Query must be deterministic – Query might provide same result no matter how many times it is run, if data remains the same. So if query works with current data, uses non-deterministic functions such as UUID(), RAND(), CONNECTION_ID() etc it will not be cached.

Table level granularity in invalidation – If table gets modification all queries derived from this table are invalidated at once. Most of them quite likely would not have change their result set but MySQL has no way to identify which one of them would so it gets rid of all of them. This is one of the main features which limits query cache effectiveness – if you have high write application such as forums, query cache efficiency might be pretty low due to this. There is also way to set minimal TTL or anything like it which is allowed by other caching systems. Also note – all queries are removed from cache on table modifications – if there are a lot of queries being cached this might reduce update speed a bit.

Fragmentation over time – Over time Query Cache might get fragmented, which reduces performance. This can be seen as large value of Qcache_free_blocks relatively to Qcache_free_memory. FLUSH QUERY CACHE command can be used for query cache defragmentation but it may block query cache for rather long time for large query caches, which might be unsuitable for online applications.

Limited amount of usable memory – Queries are constantly being invalidated from query cache by table updates, this means number of queries in cache and memory used can’t grow forever even if your have very large amount of different queries being run. Of course in some cases you have tables which are never modified which would flood query cahe but it unusual. So you might want to set query cache to certain value and watch Qcache_free_memory and Qcache_lowmem_prunes – If you’re not getting much of lowmem_prunes and free_memory stays high you can reduce query_cache appropriately. Otherwise you might wish to increase it and see if efficiency increases.

Demand operating mode If you just enable qury cache it will operate in “Cache everything” mode. In certain caches you might want to cache only some of the queries – in this case you can set query_cache_type to “DEMAND” and use only SQL_CACHE hint for queries which you want to have cached – such as SELECT SQL_CACHE col from foo where id=5. If you run in default mode you can also use SQL_NO_CACHE to block caching for certain queries, which you know do not need to be cached.

Counting query cache efficiency There are few ways you can look at query_cache efficiency. First looking at number of your selects – Com_select and see how many of them are cached. Query Cache efficiency would be Qcache_hits/(Com_select+Qcache_hits). As you can see we have to add Qcache_hits to Com_select to get total number of queries as if query cache hit happens Com_select is not incremented. But if you have just 20% Cache hit rate does it mean it is not worth it ? Not really it depends on which queries are cached, as well as overhead query cache provides. One portion of query cache overhead is of course inserts so you can see how much of inserted queries are used: Qcache_hits/Qcache_inserts Other portion of overhead comes from modification statements which you can calculate by (Com_insert+Com_delete+Com_update+Com_replace)/Qcache_hits
. These are some numbers you can play with but it is hard to tell what is good or bad as a lot depends on statement complexity as well as how much work is saved by query cache.

Now lets speak a bit about Query Cache configuration and mainance. MySQL Manual is pretty good on this: Query Cache Query Cache Status Query Cache Configuration

I would just mention couple of points – as protection from one query wiping your all query cache option query_cache_limit was implemented which limits result set which can be stored in query cache. If you need larger queries to be cached you might increase it, if you most important queries are smaller you can decrease it. The other one is Qcache_lowmem_prunes – This one is used to identify if you have enough memory for query cache. Note however due to fragmentation lowmem_prunes can be triggered even if there is some free space, just badly fragmented.

Looking at performance I’ve seen query cache offering about double performance for simple queries with select done by primary key, obviously there is no upper boundary – Very complex queries producing small result set will be offering best speed up.

So when it is good idea to use query cache ?

Third party application – You can’t change how it works with MySQL to add caching but you can enable query cache so it works faster.

Low load applications – If you’re building application which is not designed for extreme load, like many personal application query cache might be all you need. Especially if it is mostly read only scenario.

Why Look for alternatives ?

There are few reasons why Query Cache might be not cache for your application:

It caches queries Application objects might need several queries to compose so it is efficient to cache whole objects rather than individual queries.

No control on invalidation Table granularity is often too bad. With other caches you may implement version based or timeout based invalidation which can offer much better hit ratio for certain application.

It is not that fast Query Cache is fast compared to running the queries but it is still not as fast as specially designed systems such as memcached or local shared memory.

It can’t retrieve multiple objects at the same time You have to query cache object by object which adds latency, there is no way you can request all objects you need to be retrieved at the same time (again memcached has it)

It is not distributed If you have 10 slaves and use query cache on all of them cache content will likely be the same, so you have multiple copies of the same data in cache effectively wasting memory. Distirbuted caching systems can effectively use memory on multiple systems so there is no duplication.

Memcached is probably the most popular distributed caching system and it works great. I should write an article comparing performance of various caching systems some time.

Share this post

Comments (76)

  • white picture frames

    First, in SEO or search engine optimization, part of the algorithm for ranking your page on search engines is determined by the number and quality of backlinks to your site, providing they occur naturally and you have not purchased them.

    July 27, 2006 at 12:00 am
  • Kevin Burton

    It certainly depends on people’s implementations but I’ve found thhat in the type of apps that I work on the query cache is almost pointless. It just ends up being invalidated all the time so I just set the cache size to zero to avoid even wasting any memory.

    I find my real boosts are when I use php with memcached and squid or mod_cache on Apache.


    July 28, 2006 at 8:30 pm
  • peter

    Thank you Kevin,

    Yes sure. In your case you’re getting a lot of updates so queries could be constantly invalidated.
    Actually it is good way to estimate if query cache is going to work for you – look at how frequently your tables are invalidated
    and how many same queries you’re going to get during this time. In some cases invalidation even once per second is not too bad as there are so many same queries it pays of, in others even once per minute may be too bad.

    I’m with you on memcached and server side proxy caching whenever proxy when possible.

    July 30, 2006 at 9:46 am
  • yogesh jadhav (India - mumbai - vashi)

    is there any tool or application which we can use to bulid Database and use our queries. and then that queries can be Optimiz using that tool or application

    December 8, 2006 at 12:04 am
  • peter


    I have no idea what do you mean by your question.

    December 8, 2006 at 11:04 am
  • mister scruff

    sounds like yogesh hasnt heard of the “explain” command in mysql.

    March 7, 2007 at 10:32 am
  • saumendra

    The MyISAM key cache retains index blocks in RAM for fast access, with both default and custom-created caches being available. Its just like the code cache in oracle.

    March 12, 2007 at 2:23 am
  • Balaji

    I am facing a strange problem with Query cache when used with JDBC. I am using MySQL5.0.37. I have Query cache enabled and configured as 64MB.

    I perform the following operations
    1.SELECT * from tableA
    2.UPDATE tableA
    3.SELECT * from tableA

    When the above mentioned operation is performed in JDBC, step#1 fills the cache and steps#2 fails to invalidate the cache. Hence step#3 gets the old results from the cache.

    The same set of operations are working when tried with a query browser.

    My application is not a transaction based. Even i tried AUTOCOMMIT=1.

    I will appreciate any ideas about this problem.

    March 26, 2007 at 3:00 pm
  • Fima

    Is there a way to ask mysql the size of the result set prior to actualy querying for it? Some result sets are too large and/or too small for my application and I’d rather not query for them/receive them.

    Also, the obvious follow up question is whether or not it will improve overall application performance. =)

    Thanks in advance for your responses.

    May 22, 2007 at 10:44 pm
  • peter

    Fima, you can of course run count(*) query to see how many results are where but it may not be fast.
    you can also add LIMIT 1000 for example to result set and if you got 1000 results you will know you likely got incomplete result in the application.

    May 23, 2007 at 12:46 am
  • anonymous helper

    if you want to know how many for sure up front, put SQL_CALC_FOUND_ROWS after your select. You can then SELECT FOUND_ROWS() to get the count of all possible items if you would get without a limit. I would do the initial query with LIMIT 0 if you don’t want anything actually returned to you. Realistically, do a limit of 100, and you will also have the max number from the second query, and decide from there how to proceed.


    June 20, 2007 at 10:52 am
  • Gerry

    Some of the information in this blog entry is out of date and thus incorrect:


    > “Avoid comment (and space) in the start of the query – Query Cache does simple optimization to check if query can be cached. As I mentioned only SELECT queries are cached – so it looks at first letter of the query and if it is “S” it proceeds with query lookup in cache if not – skips it.”

    I just wasted a couple of hours on this one only to realise that the statement was incorrect and based on a bug which has long since been fixed.

    Other things in this article are wrong too. For instance some prepared statements will in fact work. Check the manual for more accurate info on what will and won’t work.

    And the Pro MySqL book that I have always documents issues such as this, but does not mention this problem and the same goes for the MySQL manual. I would have preferred if the author had specified where he was getting his info so I didn’t have to waste so much time looking into this.

    This guy tested it:

    This documents that the bug only exists in older 4.0 version of MySQL:

    February 18, 2008 at 11:47 pm
  • peter


    At the point this article was written (about 1.5 years ago) the prepared statements did not work with query cache. Even now they are only supported in MySQL 5.1 which is still not production release.

    The problem with spaces was indeed partially fixed. Basic whitespace is fixed AFAIR but comments are not.

    February 19, 2008 at 11:31 am
  • Gerry

    “AFAIR but comments are not”

    Hmmmm… as far as you remember…. but from where? Where would you have got this information?

    The second link I provided says that the comments/space issue was fixed in MySQL 5

    March 20, 2008 at 1:27 am
  • nawab

    thanks for the informative article.

    i would like to know if there is some transparent query cache available for mysql that is not ACID safe. something to work with high update scenario, the cache is NOT dropped with every table change. memcached is one option, but it is not transparent, i.e., i need to modify in application every place where an expensive mysql query is being done. i would rather prefer a transparent way, e.g., something implemented as a wrapper over JDBC classes with local in-memory caching. or a set of APIs that first query the memcached/JCS/EHCache and if nothing is found, query the MySQL database.


    May 10, 2008 at 9:25 am
  • peter


    Typically people would use either memcache or query cache. Invalidation is important part of being transparent – if you do not invalidate you get stale data from cache so it is not transparent for application any more – application needs to be aware it is getting the stale data. And what if it needs the most recent one ?

    Using memcache or other cache explicitly allows control over how invalidation works.

    May 10, 2008 at 11:40 pm
  • Aijaz

    I have created a table which has 1 lakh of records but the problem is ..when m trying to perform a Query on this table … it consists of 3-4 tables joins … so it takes around 7-8 mins to fetch the data … Then i tried to Built Index on few columns…. But when we Explain that Query … thn also it shows ALL instead of the index type of any

    July 2, 2008 at 11:31 pm
  • pratap

    Please clarify that whether it is possible to cache data of whole table in memory so that queries are fast in MYSQL ?

    July 16, 2008 at 4:22 am
  • peter


    This is common misconception. Query Cache caches query Result it does not cache any data from the base tables. Other caches exist for that purpose.

    July 20, 2008 at 9:17 am
  • vikram

    how to optimize the select query with round() func…

    September 26, 2008 at 2:46 pm
  • vikram

    as my select query is taking 3 secs time i want to optimize it to 1 secs and
    my query is like this “SELECT round(sum(i.withdrawrealwin)) FROM invoice i where i.raceno=’1′”
    i want to reduce the execution time
    please give me a solution

    September 26, 2008 at 2:48 pm

    Could have been nice if query cache could have been instructed to avoid certain tables.

    In my db a few tables are frequently updated thus not suitable for query cache while others are seldom updated and perfect for it. Current MySQL configuration is really uneasy since I’ll have to specify whether to cache or not per query.

    Anyway, using APC (or memcached if on multiple machines) is a much better choice as it’s much faster than MySQL’s query cache.

    October 19, 2008 at 1:07 am
  • Thiru

    Thanks for the nice post, Peter.

    You mentioned that FLUSH QUERY CACHE needs to be run to defragment the free blocks. The Qcache_free_blocks in my server had some high value (~1500) few days back but when i checked today, it was reduced to 3. No one ran FLUSH QUERY CACHE in the server. I wonder what triggered the defragmentation? Is the query_cache completely reset (so that the fragments are also cleaned up) when it runs out of memory (pruning)? I was planning to have a cron to run FLUSH QUERY CACHE on weekly basis but may be it isn’t required. But i don’t understand how and who takes care of the defragmentation? Thanks for your time.

    December 23, 2008 at 1:02 pm
  • Thiru

    follow up to my previous question..

    or the query_cache is completely reset (including the fragmentation) when all the results in the cache are invalidated at once?

    Can OPTIMIZE TABLE invalidate the query results of the associated table? If so, it makes sense as all the tables are periodically OPTIMIZEd in my case.

    December 23, 2008 at 1:10 pm
  • peter


    The free space is merged so if you invalidate all results from query cache. This is probably the reason in your case.

    December 23, 2008 at 2:47 pm
  • Salman Akram

    Almost all of our db is written in Stored Procedures and some stored functions too (they are using prepare statement as well). I guess I have been facing the issue of query cache overhead as discussed above so I was thinking to cache only selective queries. Now the problem is when you say that Stored functions still cannot be cached does that include stored procedures too? If yes, then the only solution left is memcached?

    THanks for the help

    March 31, 2009 at 6:36 am
  • Reindl Harald

    > Anyway, using APC (or memcached if on multiple machines) is a much
    > better choice as it’s much faster than MySQL’s query cache.

    What a stoopid comment!
    This are two different shoes!

    APC is a bytecode-cache and prevent php to parse the whole code on every call
    This has nothing to to with query-cache and really NOTHING
    If you are smart you use eaccelerator/apc AND query-cache and you get response times with 0.015 seconds for a whole page as we do on our servers

    April 14, 2009 at 2:49 am
  • Tom

    I am experiencing serious bottlenecks which are crashing Apache on my server and I believe they are related to mass query cache invalidation of frequently modified tables. Also I think this is more sensible as in many cases there would be cache duplication with APC with MySQL invalidating unused cached queries. I have decided to set query_cache_type to DEMAND. Fingers crossed!

    April 22, 2009 at 7:22 pm
  • Dieter@be

    Reindl Harald,
    APC does more then just opcode caching. You can also use it as misc object cache from inside php, somewhat comparable to memcached but without networking and in the same process (eg no context switches) which makes it faster then memcached but less useful when you have more then 1 webserver.


    June 2, 2009 at 2:10 am
  • Reindl Harald

    > APC does more then just opcode caching.
    > You can also use it as misc object cache from inside php

    This are still different shoes

    And even if you cache whole pages/parts that way you should combine it with mysql-qery-cache because if your manual cache is outdated you get most time parts of it from query cache without to do anything.

    On a good configured server with well designed applications it looks like following
    Up for: 2d 16h 57m 3s (7M q [32.419 qps], 224K conn, TX: 8B, RX: 1B)
    [OK] Key buffer hit rate: 99.7% (14M cached / 44K reads)
    [OK] Query cache efficiency: 87.9% (5M cached / 6M selects)

    On this machine are 200 domains with generate times 0.010 seconds for a whole page
    Many parts (objects) of the pages are stored in a mysql-caching table because its stoopid to use memcached/shm for caching on a shared-hosting. So you are independent of apc/memcached, have optimal caching, reduced queries and a secure setup with a great performance

    June 3, 2009 at 3:57 am
  • Fosiul

    To Author and others,
    Thanks for reading this.

    I am looking for an explanation on this comments that author wrote here : “But if you have just 20% Cache hit rate does it mean it is not worth it ? Not really it depends on which queries are cached, as well as overhead query cache provides.”

    My question is, How will you know which queries are cached ?? i have read Same kind of text in High performance MySQL Server book, in their its said
    “What’s a good cache hit rate? It depends. Even a 30% hit rate can be very helpful,
    because the work saved by not executing queries is typically much more (per query)
    than the overhead of invalidating entries and storing results in the cache. It is also
    important to know which queries are cached. If the cache hits represent the most
    expensive queries, even a low hit rate can save work for the server.”

    so again, Same talk which is , if I see the complex queries are saved in Query Cache that means i am getting profit from enabling query cache options.
    but question is, how will I see which queries are cached in Query Cached memory ??

    Can any one please explain to me, I would really appreciated that.

    August 13, 2009 at 1:15 am
  • peter


    Indeed it would be good to be able to see query cache contents.
    Unfortunately it is not possible in current MySQL version. So you’ve just got to guess.

    August 17, 2009 at 9:03 pm
  • LK


    I have a table which is quite big.

    I do alot of INSERTS, UPDATE and SELECT on this table.

    If the number of rows in the tables keeps on increasing, but the same SQL query is used, will query cache actually help?

    September 9, 2009 at 9:10 am
  • Carpii

    Hi LK,

    For your big table, every time it is updated, or inserted into, all queries in the cache which use that table, will be invalidated.
    Remember the Query Cache caches the result set packets, its not caching query execution plans like SQL Server does.
    So no, the Query cache will not be helpful *for that table*.

    But.. it may be beneficial to your server as a whole, depending on what other queries are being run.

    What I would do in your case is to change all your queries which use your big volatile table, and add SQL_NO_CACHE to them.
    This means mysql wont bother wasting time caching the results, only for them to be invalidated later.
    This will also improve cache efficiency and potentially decrease fragmentation, meanwhile all your other queries will still benefit from it 🙂

    September 30, 2009 at 4:08 am
  • sillyxone

    “It can’t retrieve multiple objects at the same time You have to query cache object by object which adds latency, there is no way you can request all objects you need to be retrieved at the same time (again memcached has it)”

    Consider this query, given that Visit is the intersection entity between Consultant and Student (one-many-one):

    SELECT AS Consultant__id,
    Consultant.firstname AS Consultant__firstname,
    Consultant.lastname AS Consultant__lastname,
    Visit.consultant AS Visit__consultant,
    Vist.student AS Visit__student,
    Visit.login_time AS Visit__login_time, AS Student__id,
    Student.firstname AS Student__firstname,
    Student.lastname AS Student__lastname
    FROM Consultant INNER JOIN Visit ON Visit.consultant =
    INNER JOIN Student ON Visit.student =
    WHERE ….

    This query is certainly cachable. The best part is you can have a function that map a row into multiple objects, such as

    function map_fields($row, $prefix = ”) {
    $this->id = $row[$prefix . ‘id’];
    $this->firstname = $row[$prefix . ‘firstname’];

    so calling like this would do:
    $visit->map_fields($row, ‘Visit__’);
    $student->map_fields($row, ‘Student__’);
    $consultant->map_fields($row, ‘Consultant__’);

    Obviously, it’s still slow comparing to memcache (if you cache all the objects) as mapping to object has to happen, but at least the caching is managed transparently from the application, lifting the burden of managing cached memory from the application level. Also, caching all the objects might take more memory than letting MySQL caching the raw data.

    November 17, 2009 at 3:42 pm
  • Ilan Hazan

    Very good and comprehensive post. However, some of the information is out of date and thus incorrect (good for us that MySQL is improving in time).
    I have noticed that there are many Query Cache Improvements in time.
    This post is describing some of them:


    December 16, 2009 at 1:26 am
  • Ilan Hazan

    Very good and comprehensive post. However, some of the information in this blog entry is out of date and thus incorrect (good for us that MySQL is improving in time).

    I have noticed that there are many Query Cache Improvements.
    This post is describing some of them:


    December 16, 2009 at 1:27 am
  • jeremy

    As a non MYSQL guy, I am pretty lost after reading this, but because I am running my own server for WordPress I need to optimize my speed settings. Is it possible for you to give me the ini file settings for wordpress for my situation?

    January 17, 2010 at 11:07 am
  • Robin

    Hello,Ask a question.
    How to expand mysql query cache?
    Only add physical memory? memcached can not cache SQL results.

    June 27, 2010 at 10:15 pm
  • Reindl Harald

    > How to expand mysql query cache?

    What about changing the settings in my.cnf to whatever you need?
    query_cache_limit = 150K
    query_cache_size = 512M
    query_cache_type = 1

    June 28, 2010 at 1:32 am
  • Carpii


    You can increase the size of the query cache in my.cnf.
    You do not want to make the cache too big though, the article explains why. If you are thinking of adding RAm to the server just to increase the query cache, then it might be that you’ve misunderstood the limitations of it.

    memcache cannot cache sql results directly, but normally you would serialise the results (from PHP for example), put them in memcache, and then check memcache before running the query next time.

    June 28, 2010 at 3:49 am
  • Nurul Ferdous

    this is really a well documented article for a nuts like me. thanks peter. keep it up.

    July 26, 2010 at 10:51 am
  • Sathish

    Can some one help clarify by doubts. I have enabled the querycache.

    I m executing a query.

    say : select Empname, Empdept, Empid from DEPT where Empid=256

    It returns a result. I have updated the Dept Name and when i execute the same query it get the previous result , not getting the updated one.

    When i remove one column say Empname and execute the query it returns the updated one.

    select Empdept, Empid from DEPT where Empid=256

    How can i get the updated result. I dont want to set the query_cache_size to 0 . Do some has any alternative/solution..??

    Thanks in advance.

    November 30, 2010 at 5:23 am
  • partha

    In one of my slave server, the system user shows invalidating query cache(table). Because of this replication getting delayed. Internally what’s happening actually?

    January 5, 2011 at 4:19 am
  • Laph

    Within my app, I always saw a high query cache fragmentation (large Qcache_free_blocks) – leading to many lowmem prunes. Raising the query cache size didn’t really help.
    Well, I solved this by calculating a better value for query_cache_min_res_unit, using this formula:
    (query_cache_size – qcache_free_memory) / qcache_queries_in_cache

    This is the average size of a cached query, which was around 2k in my case. Then I changed query_cache_min_res_unit from the default of 4k to 2k and got a much better cache utilization. I could even lower the size again.

    January 10, 2011 at 8:07 am
  • Pavel

    I noticed that in the Status column is “storing result in query cache” in phpmyadmin of running queries. Some of those will never be reused for sure. From optimization standpoint, does it make sense to run those queries with SQL_NO_CACHE so there is no time spent with storing results or there is no difference?

    January 20, 2011 at 3:37 pm
  • Carpii


    Sure, for best performance you need to gently guide MySQL as to how best to cache (or not cache things).

    If you have queries which you know cannot be cached then tell mysql not to cache them. It might save some reusable query from being evicted from the cache, and will keep your cache hit ratio up (which is a good thing).

    Also for tables which are frequently updated, it might be worth not caching some queries on those tables. When a table is updated MySQL removes all cached queries which reference those tables, from the query cache. It has to do this because it caches on a packet level, so has no real understanding of whether the cached query would be affected by the update or not

    Finally, best to avoid caching large batch which run maybe once a day.
    Also for queries which return large result sets but are run infrequently, theres usually little point in caching these either.

    January 23, 2011 at 5:51 pm
  • white picture frames

    First, in SEO or search engine optimization, part of the algorithm for ranking your page on search engines is determined by the number and quality of backlinks to your site, providing they occur naturally and you have not purchased them.

    April 12, 2011 at 11:17 pm
  • Sudheer

    Hi Peter ,

    I have one production server on high with 95% on insets and updates commands. Is query_catch help me?
    right now on my server query cache values

    query_cache_limit = 524288
    query_cache_size = 134217728

    Qcache_hits/(Com_select+Qcache_hits)=984286623/(984286623+327790129)=0.75 , (Com_insert)/Qcache_hits = (2029732811)/984286623 = 2.0621,(Com_insert+Com_delete+Com_update+Com_replace)/Qcache_hits =(2029732811+166042669+1678664993)/984286623 =3.9363

    Variable_name Value
    Qcache_free_blocks 18580
    Qcache_free_memory 53059640
    Qcache_hits 984929059
    Qcache_inserts 36451881
    Qcache_lowmem_prunes 1198358
    Qcache_not_cached 244851098
    Qcache_queries_in_cache 32379
    Qcache_total_blocks 83477

    Could you please suggest me can I go for query_catch= 0 or any other value?

    July 20, 2011 at 12:51 am
  • Leo

    Good article. Can you post one on memcached?


    July 26, 2011 at 1:41 pm
  • Akhthar

    I think the Query cache won’t be able to use the free memory available (Qcache_free_memory), if there are too many fragmented blocks (Qcache_free_blocks), right? And I believe this happens when Qcache_free_blocks becomes too much for Qcache to make any useful blocks out of free memory.

    root@server [~]# date;mysqladmin ext|awk ‘/Qcache/ && /free_blocks/ || /free_memory/ || /lowmem_prunes/’
    Thu Oct 20 03:42:03 EDT 2011
    | Qcache_free_blocks | 117773 |
    | Qcache_free_memory | 147256440 |
    | Qcache_lowmem_prunes | 1849687 |

    root@server [~]# date;mysqladmin ext|awk ‘/Qcache/ && /free_blocks/ || /free_memory/ || /lowmem_prunes/’
    Thu Oct 20 03:42:06 EDT 2011
    | Qcache_free_blocks | 117694 |
    | Qcache_free_memory | 147396720 |
    | Qcache_lowmem_prunes | 1849719 |

    As you can see the Qcache pruning due to low memory occurs even when there are around 140M+ free memory in Qcache.

    October 20, 2011 at 1:56 am
  • Kalpesh

    Hello Peter

    First of all thank a lot for putting this website together, I have referenced your material many times to solve issues and it has worked always.

    I have following query in my application, and noticed that data is not cached at all. I checked all the settings and my settings says it should be cached.

    Only thing I can think of now is, this SQL may be “complex” and that is the reason its not cached by MySQL. So is there any other option to cache it “force cache” ?

    select ea.string_value
    , cast((e.DATE – interval weekday(e.DATE) day) as date) AS WeekStart
    , (count(0) /sum(e.ITEM_COUNT)*100) AS Percent
    from db_three.table1 en
    join db_one.table2 ea on and (‘0’=’0’ or en.Id in (0))
    join db_one.table3 a on =1 and =
    join db_two.table4 on table4.TYPE = ‘DATA_SOURCE_TYPE’
    join db_two.table5 e on e.object_name = en.object_name
    and e.table4_type_id = table4.ID
    Group by 1,2
    Order by 2,1


    Show variables like %cache%;
    Binlog_cache_disk_use, 0
    Binlog_cache_use, 0
    Com_assign_to_keycache, 0
    Qcache_free_blocks, 1
    Qcache_free_memory, 536834160
    Qcache_hits, 0
    Qcache_inserts, 2
    Qcache_lowmem_prunes, 0
    Qcache_not_cached, 15
    Qcache_queries_in_cache, 2
    Qcache_total_blocks, 11
    Ssl_callback_cache_hits, 0
    Ssl_session_cache_hits, 0
    Ssl_session_cache_misses, 0
    Ssl_session_cache_mode, NONE
    Ssl_session_cache_overflows, 0
    Ssl_session_cache_size, 0
    Ssl_session_cache_timeouts, 0
    Ssl_used_session_cache_entries, 0
    Threads_cached, 0

    November 2, 2011 at 7:28 am
  • Straw Man

    I’m new to all this SQL stuff and wish I had enough knowledge to read and comprehend everything you are saying, however, if you would be so kind and point me in the right direction of a good “beginner’s SQL boo” I would really appreciate it?
    Consider me the straw-man from the Wizard of Oz…. the government does. (mentally full of hay but willing to learn). Thanks

    November 19, 2011 at 7:53 pm
  • Muhammad Omair

    We have query_cache_type= DEMAND but still a single query that contains multiple joins with four tables uses “checking query cache” state even though the SQL SELECT statement does not contain SQL_CACHE in the begining. The strange thing is that it only happens for these four tables. Would there be any reason for it.

    March 21, 2012 at 4:46 am
  • Brent Wilson

    Would Query Cache be useful on a forum software such as xenForo?

    April 12, 2012 at 4:52 am
  • repls

    Hi Peter,

    From the Percona Server operations manual i know that you have do something change to the query_cache_type. it says “Note: This variable already exists in standard MySQL, but when setting query_cache_type=0, the query cache mutex
    will still be in used. Setting query_cache_type=0 in Percona Server ensures that both the cache is disabled and the
    mutex is not used.”

    so the question comming up: in standard MySQL, when disable query cache by setting query_cache_type=0, why the query cache mutex will still by in used ? nothing related to the query cache why it still use query cache mutex?

    January 16, 2013 at 1:32 am
  • Amir

    I’m using joomla and wordpress for my sites.
    I want to know: can I save result of some queries in user session and read from session. so the query will run one time in each session. I’m thinking for this for improve the speed of my site.

    August 28, 2013 at 3:28 am
  • Paul


    You could store them in session if you wanted, but its often not a great idea.
    You’d be better off using some key store like memcache or redis

    Its not really an SQL question.

    August 28, 2013 at 6:55 am
  • Jony

    Recently my server CPU has been going very high.

    Currently, My my.cnf file such as below:
    key_buffer = 128M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 2M
    sort_buffer_size = 2M
    read_rnd_buffer_size = 2M
    table_cache = 1024
    record_buffer = 1M
    thread_cache_size = 128
    wait_timeout = 30
    connect_timeout = 10
    interactive_timeout = 10
    tmp_table_size = 64M
    max_heap_table_size = 64M
    max_allowed_packet = 16M
    max_connect_errors = 10
    query_cache_limit = 1M
    query_cache_size = 64M
    query_cache_type = 1

    CPU Usage: u623.84 s78.87 cu0 cs0 – 8.12% CPU load
    MYSQLD: 190%

    My server information:
    Intel® Pentium® E5400 2.70GHz 2M Cache
    HDD: 250GB
    RAM: 4GB

    Please help me!

    April 26, 2014 at 2:32 am
  • Carpii

    Jony, you should use the forums for help

    But you haven’t configured innodb at all that I can see, yet default storage engine.
    You need to a least dedicate a significant amount of ram to innodb_buffer_pool_size, but other configs are important too

    April 26, 2014 at 10:40 am
  • Web Host

    Can you post some examples for a tuned up my.cnf file? Say a server with about 100 websites running mysql pretty active.

    November 15, 2016 at 2:28 am
  • Bayur

    Thank you, very much for the information you gave me

    November 27, 2016 at 1:36 pm
  • Moda


    I have a table which is quite big.

    I do alot of INSERTS, UPDATE and SELECT on this table.

    January 7, 2017 at 2:47 pm
    • Roland G. Lee

      If you have a lot of writes to the database, caching would be of less value. Caching makes sense when you have read-heavy workloads.

      May 30, 2017 at 10:10 pm
  • odtülüler dershanesi kızılay

    thanks for this article. i will use this config my website

    March 27, 2018 at 5:41 pm

    thanks for this article. i will use this config my website

    February 3, 2019 at 2:56 pm
  • maurers

    I recommend this site everyone.

    July 3, 2019 at 5:55 pm

    thanks for this article. i will use this config my website

    July 31, 2019 at 9:07 am
  • ankara ambar

    i will use this config my website very impressive

    August 6, 2019 at 5:53 pm
  • reductil

    good post thank u

    September 17, 2019 at 8:09 pm
  • fx15 sipariş

    fx15 liked this post

    November 25, 2019 at 9:42 pm
  • ankara dershane

    beneficial content

    December 27, 2019 at 4:00 am
  • Flexispy

    MySQL has a great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you need to know.


    January 6, 2020 at 9:20 am
  • lida

    good post

    March 24, 2020 at 7:43 am
  • Ankara'nın En iyi Dershanesi

    Great Post, thank you

    June 8, 2020 at 9:30 pm

Comments are closed.