Description:
When doing queries on a partitioned table all qcache entries for that table will be invalidated as soon as one partition gets an update - even if qcache entries do not relate to an updated partition. When you have a partitioned table where only one partition gets permanent updates for a certain time, queries should remain in qcache when they not contain data from this partition. Cacheing should behave like if it were different tables to achieve better qcache utilization. For huge tables, what partitioned tables are made for, this would help to reduce expensive io-operations.
How to repeat:
CREATE TABLE t1 (
a INT,
b varchar(32),
KEY (a))
ENGINE = MyISAM
PARTITION BY LIST (a)(
PARTITION p0 VALUES IN (0) ENGINE = MyISAM,
PARTITION p1 VALUES IN (1) ENGINE = MyISAM,
PARTITION p2 VALUES IN (2) ENGINE = MyISAM
);
insert into t1 values(0,'0'),(1,'1'),(2,'2');
set profiling = 1;
flush status;
show status like '%Qcache%';
select sql_cache * from t1 where a in(1,2);show status like '%Qcache%';
select sql_cache * from t1 where a in(1,2);show status like '%Qcache%';
insert into t1 values(0,'00');show status like '%Qcache%';
select sql_cache * from t1 where a in(1,2);show status like '%Qcache%';
mysql-> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------+
..
| 3 | 0.00025200 | select sql_cache * from t1 where a in(1,2) |
..
| 5 | 0.00001800 | select sql_cache * from t1 where a in(1,2) |
..
| 7 | 0.00014300 | insert into t1 values(0,'00') |
..
| 9 | 0.00014100 | select sql_cache * from t1 where a in(1,2) |
..
+----------+------------+--------------------------------------------+
Query 5 is read from qcache (show its profile). Query 9 comes not from qcache(show its profile), though it should. As select queries are only related to partitions 1 and 2 which actually should not be touched by insert query.
Suggested fix:
Invalidate qcache entries only when they relate to updated partitions. Alternatively / additionally allow multiple qcaches, each assignable to particular tables or partitions.
