Description:
Hi,
It seems the use of the query cache depends on the currently selected DB, even if a DB is explicitly specified in the query.
This also means duplicated resultset could be stored in the query cache.
Regards,
Jocelyn Fournier
How to repeat:
RESET QUERY CACHE;
DROP DATABASE IF EXISTS t1;
CREATE DATABASE t1;
DROP DATABASE IF EXISTS t2;
CREATE DATABASE t2;
use t1;
CREATE TABLE a (a int);
INSERT INTO a VALUES (1),(2);
SET PROFILING=1;
SELECT * FROM t1.a WHERE a=1;
SELECT * FROM t1.a WHERE a=1;
SHOW PROFILE FOR QUERY 1;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| (initialization) | 0.000001 |
| checking query cache for query | 0.0000197 |
| Opening tables | 0.0000042 |
| System lock | 0.0000017 |
| Table lock | 0.0000177 |
| init | 0.0000115 |
| optimizing | 0.000006 |
| statistics | 0.0000075 |
| preparing | 0.0000072 |
| executing | 0.0000017 |
| Sending data | 0.0000412 |
| end | 0.000002 |
| query end | 0.000002 |
| storing result in query cache | 0.0000022 |
| freeing items | 0.000003 |
| closing tables | 0.000002 |
| logging slow query | 0.000001 |
+--------------------------------+-----------+
SHOW PROFILE FOR QUERY 2;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| (initialization) | 0.0000012 |
| checking query cache for query | 0.0000027 |
| checking privileges on cached | 0.000002 |
| sending cached result to clien | 0.000011 |
| logging slow query | 0.000001 |
+--------------------------------+-----------+
Ok, now the query is in cache.
use t2;
SELECT * FROM t1.a WHERE a=1;
SELECT * FROM t1.a WHERE a=1;
SHOW PROFILE FOR QUERY 4;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| (initialization) | 0.0000017 |
| checking query cache for query | 0.0000387 |
| Opening tables | 0.0000062 |
| System lock | 0.000004 |
| Table lock | 0.0000237 |
| init | 0.000014 |
| optimizing | 0.0000062 |
| statistics | 0.0000085 |
| preparing | 0.0000072 |
| executing | 0.000002 |
| Sending data | 0.0000532 |
| end | 0.0000025 |
| query end | 0.0000017 |
| storing result in query cache | 0.0000027 |
| freeing items | 0.000004 |
| closing tables | 0.0000025 |
| logging slow query | 0.0000012 |
+--------------------------------+-----------+
As you can see, even if the query is the same, because we have selected another DB, the query cache doesn't consider it in cache.
SHOW PROFILE FOR QUERY 5;
+--------------------------------+-----------+
| Status | Duration |
+--------------------------------+-----------+
| (initialization) | 0.000002 |
| checking query cache for query | 0.0000037 |
| checking privileges on cached | 0.0000032 |
| sending cached result to clien | 0.000013 |
| logging slow query | 0.0000012 |
+--------------------------------+-----------+
Now it's in cache.
It means we now have two times the result of the same query.
I assume inside the query cache, we know the target DB since we need to check privileges, so this target DB should be used instead of the current DB (at least if no crossDB selects are involved) ?
