How to list cached queries in MySQL? (Qcache_queri

Question

Show status like 'Qcache_queries_in_cache' returns:

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_queries_in_cache | 327      |
+-------------------------+----------+

How do I print these 327 queries?

In an attempt to optimize mysql caching I want to trying switching to "on demand" caching. But before I do I want to get a definitive sense of which queries are being cached or discarded. I tried mysql docs, google, and stackoverflow search but no luck.

Answer 1

AFAIK sql queries are not stored into Qcache only their hash. So there is no way to find what queries are now cached exept you execute one of your query and see the changes of Value column.

Answer 2

If You enable a Session Specific variable profiling

SET SESSION PROFILING=on;
show profiles;

Now by checking show profile for query your query_id;

mysql> show profile for query 2;

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000017 |
| checking query cache for query | 0.000005 |
| checking privileges on cached  | 0.000003 |
| sending cached result to clien | 0.000005 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000001 |
+--------------------------------+----------+

By checking the status column you can see whether the queries cached.

But this is only a session specific you have enable profiling for each session.