Introduction to MySQL Query Cache: Part I
Today morning, I was monitoring MySQL Production database.
I found out list of running sessions with different SQL queries. I had created some master and primary tables in MySQL which stored settings and configuration data for one of our Mobile App.
As these are tables for configuration purpose so not frequently update by application.
But, Today I was worried because this configuration table is taking some level of CPU I/O. I created this all tables under InnoDB MySQL Storage engine.
I cannot store all this data under Memory engine because this all are primary data for our Application.
So I was finding a solution for faster retrieval and found MySQL has one good option called a Query Cache.
In this post, I just explain a basic theory of MySQL Query Cache and I am also preparing the second part for practical configuration of MySQL Query Cache.
What is MySQL Query Cache ?
Sort & Simple, MySQL Query Cache stores some frequent SELECT statements with its corresponding results.
When table data are not changing frequently and the server receives many identical queries then only Query Cache works for better performance.
Whenever table modified with data, then Query cache will be flush.
I am surprised by this feature of MySQL which improve your query performance in some circumstances.
When you trigger any select statement and if it is available in the cache then it will return from there only not require parsing and fetching from disk so its improve a query performance.
Please do not assume that this will work under all circumstances because this is also depends on your load of memory and server.
You can also set the size for Query Cache but make sure that it should not be more higher otherwise its create overhead.
Below is a some conditions in which Query Cache will not work:
- Not use when multiple server hitting to one query.
- Not use with Stored Procedure and UDF.
- Not use with any type of Variables.
- Not use with any table of default database like, MySQL, INFORMATION_SCHEMA.
- Not use with partition tables.
- Not use with Temporary tables.
- Not use with Prepared Statements.
This all basic about MySQL Query Cache: