I tested several simple queries like such:
SELECT COUNT(*) FROM Users WHERE courses='a';
But none are being cached. The following are the cache related variables:
show variables like "%cache%";
+-------------------------------+----------------------+
| Variable_name | Value |
+-------------------------------+----------------------+
| aria_pagecache_age_threshold | 300 |
| aria_pagecache_buffer_size | 100000000 |
| aria_pagecache_division_limit | 100 |
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| have_query_cache | YES |
| join_cache_level | 2 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| key_cache_segments | 0 |
| max_binlog_cache_size | 18000000000000000000 |
| max_binlog_stmt_cache_size | 18000000000000000000 |
| metadata_locks_cache_size | 1024 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_strip_comments | OFF |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| stored_program_cache | 256 |
| table_definition_cache | 400 |
| table_open_cache | 400 |
| thread_cache_size | 0 |
+-------------------------------+----------------------+
The cache related status shows:
show status like "%cache%";
+-----------------------------------+-------+
| Variable_name | Value |
+-----------------------------------+-------+
| Aria_pagecache_blocks_not_flushed | 0 |
| Aria_pagecache_blocks_unused | 15737 |
| Aria_pagecache_blocks_used | 2 |
| Aria_pagecache_read_requests | 78 |
| Aria_pagecache_reads | 3 |
| Aria_pagecache_write_requests | 6 |
| Aria_pagecache_writes | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Com_assign_to_keycache | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_used_session_cache_entries | 0 |
| Subquery_cache_hit | 0 |
| Subquery_cache_miss | 0 |
| Threads_cached | 0 |
+-----------------------------------+-------+
I have read through the manual and checked that the following conditions are satisfied:
- the query_cache_type server variable is set to 1, or ON
- the following functions are not present in the query: BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME() DATABASE() ENCRYPT() (one parameter) FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() SLEEP() SYSDATE() UNIX_TIMESTAMP() (no parameters) USER() UUID() UUID_SHORT()
it is not of the form:
SELECT SQL_NO_CACHE ... SELECT ... INTO OUTFILE ... SELECT ... INTO DUMPFILE ... SELECT ... FOR UPDATE SELECT * FROM ... WHERE autoincrement_column IS NULL SELECT ... LOCK IN SHARE MODE
- it uses a non-TEMPORARY table
- it does not generate a warning
- it does not access a partitioned table
- it does not access a table from INFORMATION_SCHEMA, mysql or the performance_schema database
- it does not make use of user or local variables
- it does not make use of stored functions
- it does not make use of user-defined functions
- it is not inside a transaction with the SERIALIZABLE isolation level
The only thing I am not sure is:
- the user does not have a column-level privilege on any table in the query
I run SHOW FULL COLUMNS FROM Users; and all columns contain "select, insert, update". Even newly created tables contain those privileges. Can anyone explain what could be wrong?
