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:
PLAIN TEXT
SQL:

1. [FONT='Courier New', Courier, monospace]CREATE TABLE xp.test1 (id int, name varchar(23));[/font]
   
2. [FONT='Courier New', Courier, monospace]INSERT INTO xp.test1 VALUES (5, 'testit');[/font]
   
3. [FONT='Courier New', Courier, monospace]GRANT SELECT (id) ON TABLE xp.test1 TO 'vadim'@'localhost'; [/font]

and under user 'vadim'
PLAIN TEXT
SQL:

1. [FONT='Courier New', Courier, monospace]Before query execution:[/font]
   
2. [FONT='Courier New', Courier, monospace]mysql> SHOW global STATUS LIKE 'qcache%';[/font]
   
3. [FONT='Courier New', Courier, monospace]+-------------------------+----------+[/font]
   
4. [FONT='Courier New', Courier, monospace]| Variable_name | Value |[/font]
   
5. [FONT='Courier New', Courier, monospace]+-------------------------+----------+[/font]
   
6. [FONT='Courier New', Courier, monospace]| Qcache_free_memory | 67091176 | [/font]
   
7. [FONT='Courier New', Courier, monospace]| Qcache_hits | 0 | [/font]
   
8. [FONT='Courier New', Courier, monospace]| Qcache_inserts | 0 | [/font]
   
9. [FONT='Courier New', Courier, monospace]| Qcache_queries_in_cache | 0 | [/font]
   
10. [FONT='Courier New', Courier, monospace]+-------------------------+----------+[/font]
    
11. [FONT='Courier New', Courier, monospace]8 rows IN SET (0.01 sec)[/font]
    
12. [FONT='Courier New', Courier, monospace]Query:[/font]
    
13. [FONT='Courier New', Courier, monospace]mysql> SELECT id FROM xp.test1 WHERE id> 1;[/font]
    
14. [FONT='Courier New', Courier, monospace]+------+[/font]
    
15. [FONT='Courier New', Courier, monospace]| id |[/font]
    
16. [FONT='Courier New', Courier, monospace]+------+[/font]
    
17. [FONT='Courier New', Courier, monospace]| 5 | [/font]
    
18. [FONT='Courier New', Courier, monospace]+------+[/font]
    
19. [FONT='Courier New', Courier, monospace]1 row IN SET (0.00 sec)[/font]
    
20. [FONT='Courier New', Courier, monospace]After query execution:[/font]
    
21. [FONT='Courier New', Courier, monospace]mysql> SHOW global STATUS LIKE 'qcache%'; [/font]
    
22. [FONT='Courier New', Courier, monospace]+-------------------------+----------+[/font]
    
23. [FONT='Courier New', Courier, monospace]| Variable_name | Value |[/font]
    
24. [FONT='Courier New', Courier, monospace]+-------------------------+----------+[/font]
    
25. [FONT='Courier New', Courier, monospace]| Qcache_free_memory | 67089640 | [/font]
    
26. [FONT='Courier New', Courier, monospace]| Qcache_hits | 0 | [/font]
    
27. [FONT='Courier New', Courier, monospace]| Qcache_inserts | 1 | [/font]
    
28. [FONT='Courier New', Courier, monospace]| Qcache_queries_in_cache | 1 | [/font]
    
29. [FONT='Courier New', Courier, monospace]+-------------------------+----------+ [/font]

As you see the query was inserted into query cache with allocated memory for.
Let's try second execution:
PLAIN TEXT
SQL:

1. [FONT='Courier New', Courier, monospace]8 rows IN SET (0.01 sec)[/font]
   
2. [FONT='Courier New', Courier, monospace]mysql> SELECT id FROM xp.test1 WHERE id> 1;[/font]
   
3. [FONT='Courier New', Courier, monospace]+------+[/font]
   
4. [FONT='Courier New', Courier, monospace]| id |[/font]
   
5. [FONT='Courier New', Courier, monospace]+------+[/font]
   
6. [FONT='Courier New', Courier, monospace]| 5 | [/font]
   
7. [FONT='Courier New', Courier, monospace]+------+[/font]
   
8. [FONT='Courier New', Courier, monospace]1 row IN SET (0.00 sec)[/font]
   
9. [FONT='Courier New', Courier, monospace] [/font]
   
10. [FONT='Courier New', Courier, monospace]mysql> SHOW global STATUS LIKE 'qcache%'; [/font]
    
11. [FONT='Courier New', Courier, monospace]+-------------------------+----------+[/font]
    
12. [FONT='Courier New', Courier, monospace]| Variable_name | Value |[/font]
    
13. [FONT='Courier New', Courier, monospace]+-------------------------+----------+[/font]
    
14. [FONT='Courier New', Courier, monospace]| Qcache_free_memory | 67089640 | [/font]
    
15. [FONT='Courier New', Courier, monospace]| Qcache_hits | 0 | [/font]
    
16. [FONT='Courier New', Courier, monospace]| Qcache_inserts | 1 | [/font]
    
17. [FONT='Courier New', Courier, monospace]| Qcache_queries_in_cache | 1 | [/font]
    
18. [FONT='Courier New', Courier, monospace]+-------------------------+----------+[/font]
    
19. [FONT='Courier New', Courier, monospace]8 rows IN SET (0.02 sec) [/font]

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.


Query_cache and column level privileges - Read More...