Updated May 26, 2023
Definition of MySQL Query Cache
MySQL Query Cache is a noticeable MySQL feature responsible for hustling up the data recovery from a database server. Keeping the SELECT MySQL statements and the fetched record collection in memory achieves this. As a result, when a client requests similar queries, it can retrieve the data faster without executing the commands against the database again. If we compare to data reading from disk, the cached information from the RAM takes a littler access time that diminishes the latency, thus refining I/O(Input/Output) operations. Let’s consider an illustration of a WordPress website or any E-commerce portal/website that experiences high read calls and sporadic data changes. Here, the query cache will boost the presentation of the MySQL database server extremely, making it more walkable.
Syntax
If we want to set up MySQL Query Cache, then we will follow the syntax of it explained as follows:
Except MariaDB has a built-in absence of query cache but will always be present while inactive. We can view the availability of query cache on the server with the variable have_query_cache described below:
SHOW VARIABLES LIKE 'have_query_cache';If you execute this query and set it to NO, you cannot enable the query cache unless you recreate or reinstall a MariaDB version that includes the cache feature. MariaDB enables the query cache by default for versions up to 10.1.6, but starting from version 10.1.7, it disables the query cache. You can enable it by setting the server variable query_cache_type to 1 if needed.
Though assisted in the versions of MariaDB prior to 10.1.7, the variable query_cache_size will be by default 0KB, disables the query cache efficiently. But after this version, the cache size defaults to 1MB. If a user wants to set or configure the query cache size to a big enough quantity, it can be done as follows:
SET GLOBAL query_cache_size = 5000000;Starting from MariaDB version 10.1.7, the query_cache_type automatically turns ON when the server is initialized by explicitly setting the query_cache_size to a non-zero or non-default value.
How does Query Cache work in MySQL?
When you enable the query cache in MySQL, the system follows a process for each new SELECT query statement. It actively processes the query and verifies its presence in the query cache. It checks whether the query has been executed before and if its results are stored in the cache. To determine if two queries are identical, the system compares their utilization of the same database server, protocol version, and default character set. Prepared statements are usually treated separately from non-prepared statements.
If any similar query does not exist in the cache, it will usually be administered and stored with its result set within the query cache. If the query is in the cache, retrieving the results is significantly faster than regular processing. The queries need to be examined in a case-sensitive manner, thus:
SELECT * FROM z;This query is different from the following:
Select * from z;In this context, the comments are also measured and will make the queries vary:
/* Demo1 */ SELECT * FROM z;This is different from the below one:
/* Demo2 */ Select * from z;If you want to strip comments before searching as an option, then view the server variable query_cache_strip_comments.
Whenever any changes are made to the records in a table, the query cache clears the entire affected result sets. Thus, fetching data from the query cache cannot be possible. The oldest outcomes will be released if the space allocated for the query cache is exhausted.
If we implement query_cache_type = NO with the query specifying SQL_NO_CACHE, i.e., case sensitive, the server cannot cache the query and retrieve results from the query cache. If we implement query_cache_type = DEMAND, i.e., after feature MDEV-6631 request, and the query states SQL_CACHE, MySQL server will perform the query cache.
Examples
MySQL Query Cache is normally responsible for tuning the MySQL performance, and one can view the variables of query cache in the database server using the following commands:
SHOW VARIABLES LIKE %query_cache%;One can even modify these variables by applying SET SESSION or SET GLOBAL query statements shown below:
SET GLOBAL query_cache_size = 18777216;The query cache is an essential feature for query optimization, improving performance. You can also display the status of the query cache variable working in the server as:
SHOW STATUS LIKE "qcache%";The MySQL query cache defines itself as a cache for query results. It matches the incoming queries that initialize with SEL to a hash table; if a match exists, it will return from the preceding execution of the query. But you will find a few restrictions mentioned below:
- The query cache escapes parsing and matches byte for byte.
- Implementing non-deterministic features can result in not caching the query that comprises temporary tables, RAND(), NOW(), UDFs, and user variables.
- The query cache aimed to prevent the serving of stale results. If any modifications occur to the underlying table(s) will result in all cache being canceled for those tables.
- If you have an open transaction, applying the query cache in InnoDB to support MVCC (Multi-Version Concurrency Control) may result in the cache not reflecting the data in your intended viewers.
In this case, non-deterministic features prevent the caching of certain query functions. Examples of such functions include BENCHMARK(), CONVERT_TZ(), CONNECTION_ID(), CURRENT_TIME(), CURDATE(), ENCRYPT(), GET_LOCK(), CURTIME(), FOUND_ROWS(), DATABASE(), and so on.
The query cache feature does not cache a query in the following cases:
- It implements no table or generates a warning.
- It applies stored functions or local or user-defined variables.
- Transaction having serialization at the isolated level.
- Having forms like:
- SELECT with SQL_NO_CACHE
- SELECT with INTO OUTFILE
- SELECT with INTO DUMPFILE
- SELECT FOR UPDATE
Conclusion
In this article, we have discussed MySQL Query Cache, a feature supported in MySQL that, when configured in the server, boosts up the SQL queries and enhances the performance of operations. MySQL version 5.7.20 deprecated the query cache feature, and MySQL 8.0 completely removed it. However, MySQL’s supported version users can still find the query cache helpful feature. For the latest versions, one may also use a third-party tool such as ProxySQL for optimizing the MySQL database performance.
Recommended Articles
We hope that this EDUCBA information on “MySQL Query Cache” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
