Recently we were puzzled by question how query_cache works with column level privileges. The question was appeared as we discovered function query_cache_send_result_to_client is called before real parsing of query, so at the moment of execution the query_cache is not able to know which columns are accessed.
Looking into source code I found out that in the case when table has column level privileges – function query_cache_send_result_to_client just stopped and returns to usual execution. So initial assumtion was that query against table with defined column privilege is just not cached, though it is not reflected in documentation.
It was worth to test it experimentally and the reality turned out more interesting.
under root:
|
1 2 3 |
create table xp.test1 (id int, name varchar(23)); insert into xp.test1 values (5, 'testit'); GRANT SELECT (id) ON table xp.test1 to 'vadim'@'localhost'; |
and under user ‘vadim’
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Before query execution: mysql> show global status like 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_memory | 67091176 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_queries_in_cache | 0 | +-------------------------+----------+ 8 rows in set (0.01 sec) Query: mysql> select id from xp.test1 where id > 1; +------+ | id | +------+ | 5 | +------+ 1 row in set (0.00 sec) After query execution: mysql> show global status like 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_memory | 67089640 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_queries_in_cache | 1 | +-------------------------+----------+ |
As you see the query was inserted into query cache with allocated memory for.
Let’s try second execution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
8 rows in set (0.01 sec) mysql> select id from xp.test1 where id > 1; +------+ | id | +------+ | 5 | +------+ 1 row in set (0.00 sec) mysql> show global status like 'qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_memory | 67089640 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_queries_in_cache | 1 | +-------------------------+----------+ 8 rows in set (0.02 sec) |
So query hits was not increased that proves the initial assumption – the query that includes tables with defined column level privileges can’t be cached – but actually even more – the query is stored in query cache area and eats memory, even the result cannot be used later.
Resources
RELATED POSTS
