- Query cache Settings
1) Verify that the server supports query caching
show variables like ‘%have_query_cache%’;
| Variable_name | Value |
| have_query_cache | YES |
2) The query cache is affected by the following three system variable values
show variables like ‘query_cache%’;
| Variable_name | Value |
1048576 | | query_cache_limit | / / to cache the maximum size of the result set
| query_cache_min_res_unit | 4096 |
| query_cache_size | | / 1048576 / decision for the allocation of the query cache memory size
| query_cache_type | OFF | / / determine how the operation of the query cache
| query_cache_wlock_invalidate | OFF |
So how does MySQL decide whether to place the query results in the query cache or not? This is determined by the variable QUERY_CACHE_TYPE.
This variable has three values: 0,1,2, which represent off, on, and demand.
Mysql > enable on (1)
If it is 0, the query cache is closed.
If it is 1, then the query always goes to the query cache first, even if the SQL_NO_CACHE is used, because the SQL_NO_CACHE does not cache the query results, but does not use the query results.
If it is 2, DEMAND does not use SQL_CACHE, and it appears that the query cache is still used
Conclusion: As long as QUERY_CACHE_TYPE is not closed, the SQL query will always use the query cache. If the cache is not hit, the execution plan of the query will be started to query the data in the table.
- SQL_CAHCE and SQL_NO_CACHE Hints
In order to test the efficiency of SQL statements, it is sometimes necessary to query without caching.
SELECT SQL_NO_CACHE… Grammar can be
The SQL_NO_CACHE is used to disable caching of query results, but it does not mean that the cache does not return results to the query.
The current SQL_NO_CACHE is interpreted in two ways:
1. If the current query does not use the existing cache of mobile game database to query, the current query will take more time
2. If the result set generated by the current query is not cached in the system’s query cache, the same query will take more time next time
SQL_CACHE means that the cache is used when querying.
The SQL_NO_CACHE is interpreted and tested as follows:
SQL_NO_CACHE means that thewww.diuxie.com query result is not cached. It does not meanthat the cache is not used to answer the query.
You may use RESET QUERY CACHE to remove all queries from the cache and then your next query should be slow again. Same effect if you change
the table, because this makes all cached queries invalid.
mysql> select count(*) from users where email = ‘hello’;
| count(*) |
| | 0
1 row in set (7.22sec)
mysql> select count(*) from users where email = ‘hello’;
| count(*) |
| | 0
1 row in set (0.45sec)
mysql> select count(*) from users where email = ‘hello’;
| count(*) |
| | 0
1 row in set (0.45sec)
mysql> select SQL_NO_CACHE count(*) from users where email = ‘hello’;
| count(*) |
| | 0
1 row in set (0.43sec)