This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Introduction to MySQL Query Cache: Part I
Today morning, I was monitoring MySQL Production database.
I found out the list of running sessions with different SQL queries. I created some master and primary tables in MySQL which stored settings and configuration data for one of our Mobile App.
As these tables are for configuration purpose so not frequently update by the application.
But today, I was worry because of this configuration table was utilizing more 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 that MySQL has a good option called a Query Cache.
In this post, I am sharing theory of MySQL Query Cache and I will share the second part of the practical configuration of MySQL Query Cache by tomorrow.
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 returns data from the Cache area.
Whenever table modified with data, then Query cache will be flush.
I 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, it will return from there and not require parsing and fetching from disk so it improves a query performance.
Please do not assume that this will work under all circumstances because this 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 higher otherwise its create memory overhead for the whole server.
Few 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.
Please visit MySQL Query Cache Part II for the configuration of Query Cache:
