EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Query Cache

MySQL Query Cache

By Aanchal SharmaAanchal Sharma

MySQL Query Cache

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

  1. MySQL NOT IN
  2. Clone Table in MySQL
  3. MySQL Show Users
  4. MySQL GROUP BY Count
C++ PROGRAMMING Certification Course
38+ Hours of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Certification Course
149+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL - Everything in SQL
253+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Certification Course
74+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Certification Course
 115+ Hours of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more