view_query_cache.test 2.27 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4
-- source include/have_query_cache.inc
#
# QUERY CACHE options for VIEWs
#
5 6 7 8 9
--disable_warnings
drop table if exists t1,t2,v1,v2,v3;
drop view if exists t1,t2,v1,v2,v3;
--enable_warnings

unknown's avatar
unknown committed
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
set GLOBAL query_cache_size=1355776;
flush status;
create table t1 (a int, b int);

# queries with following views should not be in query cache
create view v1 (c,d) as select sql_no_cache a,b from t1;
create view v2 (c,d) as select a+rand(),b from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from v1;
select * from v2;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from v1;
select * from v2;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";

drop view v1,v2;

# SQL_CACHE option
set query_cache_type=demand;
flush status;
# query with view will be cached, but direct acess to table will not
create view v1 (c,d) as select sql_cache a,b from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from v1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from v1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
select * from t1;
show status like "Qcache_queries_in_cache";
show status like "Qcache_inserts";
show status like "Qcache_hits";
drop view v1;
set query_cache_type=default;

drop table t1;

62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
#
# invalidation of view
#
create table t1 (a int);
insert into t1 values (1), (2), (3);
create view v1 as select a from t1 where a > 1;
select * from v1;
alter view v1 as select a from t1 where a > 2;
select * from v1;
drop view v1;
-- error 1146
select * from v1;
drop table t1;

#
# join view with QC
#
create table t1 (a int, primary key (a), b int);
create table t2 (a int, primary key (a), b int);
insert into t2 values (1000, 2000);
create view v3 (a,b) as select t1.a as a, t2.a as b from t1, t2;
select * from v3;
drop view v3;
drop table t1, t2;
unknown's avatar
unknown committed
86

87
set GLOBAL query_cache_size=default;