maria-discuss team mailing list archive

[Roberto Spadim <roberto@xxxxxxxxxxxxx>]

Guys new doubts about partition and query cache


drop table t1;
reset query cache;
CREATE TABLE t1
   (c DATETIME)
   PARTITION BY HASH ( YEAR(c) )
partitions 4;  *using myisam engine
insert into t1 values
('2014-01-01'),('2013-01-01'),('2012-01-01'),('2011-01-01'),('2010-01-01');
select * from t1;
select * from t1 partition (p0);
select * from t1 partition (p1);
select * from t1 partition (p2);
select * from t1 partition (p3);
---
this return:
|| *QUERY_CACHE_ID* || *SCHEMA* || *TABLE* ||
|| 1 || teste || t1 ||
|| 2 || teste || t1 ||
|| 3 || teste || t1 ||
|| 4 || teste || t1 ||
|| 5 || teste || t1 ||
---



now using innodb:
---
alter table t1 engine=innodb;
select * from t1;
select * from t1 partition (p0);
select * from t1 partition (p1);
select * from t1 partition (p2);
select * from t1 partition (p3);
select * from information_schema.QUERY_CACHE_QUERIES_TABLES;
---
this return:
|| *QUERY_CACHE_ID* || *SCHEMA* || *TABLE* ||
|| 1 || teste || t1 ||
|| 1 || teste || t1#P#p0 ||
|| 1 || teste || t1#P#p1 ||
|| 1 || teste || t1#P#p2 ||
|| 1 || teste || t1#P#p3 ||
|| 2 || teste || t1 ||
|| 2 || teste || t1#P#p0 ||
|| 2 || teste || t1#P#p1 ||
|| 2 || teste || t1#P#p2 ||
|| 2 || teste || t1#P#p3 ||
|| 3 || teste || t1 ||
|| 3 || teste || t1#P#p0 ||
|| 3 || teste || t1#P#p1 ||
|| 3 || teste || t1#P#p2 ||
|| 3 || teste || t1#P#p3 ||
|| 4 || teste || t1 ||
|| 4 || teste || t1#P#p0 ||
|| 4 || teste || t1#P#p1 ||
|| 4 || teste || t1#P#p2 ||
|| 4 || teste || t1#P#p3 ||
|| 5 || teste || t1 ||
|| 5 || teste || t1#P#p0 ||
|| 5 || teste || t1#P#p1 ||
|| 5 || teste || t1#P#p2 ||
|| 5 || teste || t1#P#p3 ||



Now my question...
Why ha_partition don't use the right partition at query cache?

this give a new problem of query cache partition prune,

update t1 set c='2009-01-01' where c='2012-01-01';

after this i have no query at query cache
the problem is

instead of remove partition #p0 (2012) and #p1 (2009), it removed all
partitions from query cache


that's the right thing to do?
maybe we should check ha_partition and query cache?

-- 
Roberto Spadim