Menu Close

MySQL query optimize with query cache

 

EDIT: Please note that query cache is been a know scalability issue for MySQL in high concurrency environments.  Query cache has been removed from MySQL on version 8.0 . Use with care!

 

Today let’s talk about a very cool tool of MySQL, the query cache.
The query cache saved the SELECT results, that’s already executed their raw data have not changed thus making the response time of query much more optimized, because it will fetch from memory rather than disk.
It can work in 3 different ways, in our my.ini within the group [mysqld]:
query_cache_type = 0
Off
query_cache_type = 1
On to all the query’s
query_cache_type = 2
On demand

But how best to use the query cache?
Considering that every time you change data in a table, it invalidates the cache of the same, as you indicated is a mapping of the frequency of tables updating and use the cache for those that do not have a great update/insert frequency, so that the cache will give a performance gain, and do not be overloading the server having to invalidate many queries.
For example, the table of system log, every user action it will insert new data in this table, so it is not a good candidate to be cached, since the table of the site news, receive updates two times a days, there’re a good candidate to be cached,

I’ll show in practice that we can achieve results with the query cache:

I’ve it set to ON DEMAND – query_cache_type = 2
I’ve a table with 100 million records, the option ON DEMAND are needed we specify that we want to select in SQL_CACHE:

    Running the query at first time:

mysql> SELECT SQL_CACHE * FROM noticia WHERE conteudo LIKE "%8555556%";
. . .
20 rows in set (2 min 15.20 sec)

At this time, warm up the cache with our consultation, he delayed the default time , but put it in cache, see the result of the same query:
mysql> SELECT SQL_CACHE * FROM noticia WHERE conteudo LIKE "%8555556%";
...
20 rows in set (0.00 sec)

The same query took 0 seconds to bring the results, it was cached.
Hoje vamos falar de uma ferramenta bem bacana do MySQL, o query cache.
But what is the size of the query cache?
Like everything in MySQL, it is configurable by the option query_cache_size values around 16m are recommended, depending on the capacity of your server.

If you do the analysis and see that most of your tables should be cached, you can use the reverse process, ie, set the variable query_cache_type = 1 this will cause all queries are cached and putting in selects that should not cache with SQL_NO_CACHE SELECT * FROM ...
But remember, use any of the following functions shall invalidate the query cache:
BENCHMARK() CONNECTION_ID() CONVERT_TZ()
CURDATE() CURRENT_DATE() CURRENT_TIME()
CURRENT_TIMESTAMP() CURTIME() DATABASE()
ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK()
LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT()
NOW() RAND() RELEASE_LOCK()
SLEEP() SYSDATE() UNIX_TIMESTAMP(
USER() UUID() UUID_SHORT()

Remember that to use the query cache, select * from ... is different from SELECT * FROM ... – Case sensitive
More information can be found on the MySQL website
marcelo altmann MySQL

Liked? Help and Share!