mysql query cache


* mysql query cache

//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache.html

"""...
The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

...
The query cache does not return stale data. When tables are modified, any relevant entries in the query cache are flushed.

...
To verify that enabling the query cache is beneficial, test the operation of your MySQL server with the cache enabled and disabled

..."""


//====
http://www.percona.com/blog/2015/01/02/the-mysql-query-cache-how-it-works-and-workload-impacts-both-good-and-bad/

roughly speaking, query cache === (query, result) dictionary

The MySQL query cache is a global one shared among the sessions.
It caches the select 'query' along with the 'result' set


mysql> SHOW STATUS LIKE "qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031320 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |


when you trigger a select query,
if it is available in the cache, it fetches from there
otherwise
it considers the query as a new one and will go to the parser.



//=== MySQL query cache has its own downsides

* Anytime you have a “frequently updated table” means you’re probably not going to get any benefit from query cache.

* On circumstances where there is no chance of identical selects and in this case it is important to set the query_cache_size and query_cache_type variable to zero

* On a highly concurrent environment with chances of query cache "mutex", which may become the source of a bottleneck.
Setting the query_cache_type to zero will avoid the query cache mutex,

* Contention often makes query cache the bottleneck instead of help when you have "many CPU cores".

* Generally, query cache should be off unless proven useful for your workload.

* Not to keep the query cache relatively high value at 256 MB as the "Qcache invalidation" becomes costly.

mysql> show variables like 'query_cache_size';
+------------------+---------+

"""...
Qcache_lowmem_prunes is the status variable which indicates how many times MySQL had to clear/prune some data from the Qcache to make space for the outputs of other queries.
..."""







//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache-configuration.html

mysql> SHOW VARIABLES LIKE 'have_query_cache';
mysql> SET GLOBAL query_cache_size = 40000;
mysql> SHOW WARNINGS\G
mysql> SHOW VARIABLES LIKE 'query_cache_size';


//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache-status-and-maintenance.html

mysql> SHOW STATUS LIKE 'Qcache%';

* Com_select === Qcache_inserts + Qcache_not_cached + queries with errors found during the column-privileges check

* total number of select queries === Com_select+ Qcache_hits+ queries with errors found by parser

* FLUSH QUERY CACHE defragment the query cache to better utilize its memory.
* After FLUSH QUERY CACHE, only a single free block remains.
[ but it does not remove any queries from the cache. ]

* RESET QUERY CACHE statement removes all query results from the query cache.
FLUSH TABLES statement also does this.


"""...
Qcache_lowmem_prunes status variable can help you tune the query cache size.
It counts the number of queries that have been removed from the cache to free up memory for caching new queries.
..."""





//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html

Query cache is "not" used for the following types:

* Queries that are a subquery of an outer query
* Queries executed within the body of a stored function, trigger, or event
* referring to user-defined functions (UDFs) or stored functions.
* referring to user variables or local stored program variables.
* referring to tables in the mysql or INFORMATION_SCHEMA system database
* referring to any partitioned tables.
* referring to TEMPORARY tables. ?

* SELECT ... LOCK IN SHARE MODE
[Statements within transactions that use SERIALIZABLE isolation level]
* SELECT ... FOR UPDATE
* SELECT ... INTO OUTFILE ...
* SELECT ... INTO DUMPFILE ...
* SELECT * FROM ... WHERE autoincrement_col IS NULL

* not using any tables.
* generating warnings.
* column-level privilege for any of the involved tables.

* containing any of the functions shown below:
[time/date, user/database/connection, lock, rand, encrypt, sleep, ...]
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() with no parameters
USER(), UUID(), UUID_SHORT()



//=== https://dev.mysql.com/doc/refman/5.1/en/query-cache-in-select.html
?
SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;
?