Edit File: query_cache_notembedded.result
set GLOBAL query_cache_size=1355776; flush query cache; flush query cache; reset query cache; flush status; drop table if exists t1, t2, t3, t11, t21; create table t1 (a int not null); insert into t1 values (1),(2),(3); create table t2 (a int not null); insert into t2 values (1),(2),(3); select * from t1; a 1 2 3 select * from t2; a 1 2 3 insert into t1 values (4); show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 2 flush query cache; show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 drop table t1, t2; create table t1 (a text not null); create table t11 (a text not null); create table t2 (a text not null); create table t21 (a text not null); create table t3 (a text not null); insert into t1 values("1111111111111111111111111111111111111111111111111111"); insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t11 select * from t1; insert into t21 select * from t1; insert into t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; insert into t3 select * from t1; insert into t3 select * from t2; insert into t3 select * from t1; select * from t11; select * from t21; show status like "Qcache_total_blocks"; Variable_name Value Qcache_total_blocks 7 show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 insert into t11 values(""); select * from t3; show status like "Qcache_total_blocks"; Variable_name Value Qcache_total_blocks 8 show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 flush query cache; show status like "Qcache_total_blocks"; Variable_name Value Qcache_total_blocks 7 show status like "Qcache_free_blocks"; Variable_name Value Qcache_free_blocks 1 drop table t1, t2, t3, t11, t21; CREATE TABLE t1 ( a INT NOT NULL PRIMARY KEY AUTO_INCREMENT ) ENGINE = MyISAM; LOCK TABLE t1 READ LOCAL; INSERT INTO t1 VALUES (), (), (); SELECT * FROM t1; a SELECT * FROM t1; a 1 2 3 SELECT * FROM t1; a UNLOCK TABLES; drop table t1; flush query cache; reset query cache; flush status; create table t1 (s1 int)// create procedure f1 () begin select sql_cache * from t1; select sql_cache * from t1; select sql_cache * from t1; end;// create procedure f2 () begin select sql_cache * from t1 where s1=1; select sql_cache * from t1; end;// create procedure f3 () begin select sql_cache * from t1; select sql_cache * from t1 where s1=1; end;// create procedure f4 () begin select sql_cache * from t1; select sql_cache * from t1 where s1=1; select sql_cache * from t1; select sql_cache * from t1 where s1=1; select sql_cache * from t1 where s1=1; end;// call f1(); s1 s1 s1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 3 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 3 show status like "Qcache_hits"; Variable_name Value Qcache_hits 0 call f1(); s1 s1 s1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 3 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 3 show status like "Qcache_hits"; Variable_name Value Qcache_hits 3 call f1(); s1 s1 s1 select sql_cache * from t1; s1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 4 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 4 show status like "Qcache_hits"; Variable_name Value Qcache_hits 6 insert into t1 values (1); select sql_cache * from t1; s1 1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 5 show status like "Qcache_hits"; Variable_name Value Qcache_hits 6 call f1(); s1 1 s1 1 s1 1 call f1(); s1 1 s1 1 s1 1 select sql_cache * from t1; s1 1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 4 show status like "Qcache_inserts"; Variable_name Value Qcache_inserts 8 show status like "Qcache_hits"; Variable_name Value Qcache_hits 10 flush query cache; reset query cache; flush status; select sql_cache * from t1; s1 1 select sql_cache * from t1 where s1=1; s1 1 call f1(); s1 1 s1 1 s1 1 call f2(); s1 1 s1 1 call f3(); s1 1 s1 1 call f4(); s1 1 s1 1 s1 1 s1 1 s1 1 call f4(); s1 1 s1 1 s1 1 s1 1 s1 1 call f3(); s1 1 s1 1 call f2(); s1 1 s1 1 select sql_cache * from t1 where s1=1; s1 1 insert into t1 values (2); call f1(); s1 1 2 s1 1 2 s1 1 2 select sql_cache * from t1 where s1=1; s1 1 select sql_cache * from t1; s1 1 2 call f1(); s1 1 2 s1 1 2 s1 1 2 call f3(); s1 1 2 s1 1 call f3(); s1 1 2 s1 1 call f1(); s1 1 2 s1 1 2 s1 1 2 drop procedure f1; drop procedure f2; drop procedure f3; drop procedure f4; drop table t1; SET @old_log_bin_trust_function_creators= @@global.log_bin_trust_function_creators; SET GLOBAL log_bin_trust_function_creators = 1; reset query cache; drop function if exists f1; create table t1 (id int); create function f1 () returns int begin declare i_var int; set i_var = sleep(3); insert into t1 values(3); set i_var = sleep(3); return 0; end;| select f1(); select sleep(4); sleep(4) 0 select * from t1; id 3 f1() 0 select * from t1; id 3 reset query cache; select * from t1; id 3 drop table t1; drop function f1; set GLOBAL query_cache_size=0; SET @@global.log_bin_trust_function_creators = @old_log_bin_trust_function_creators; DROP DATABASE IF EXISTS bug30269; FLUSH STATUS; CREATE DATABASE bug30269; USE bug30269; CREATE TABLE test1 (id int, name varchar(23)); CREATE VIEW view1 AS SELECT * FROM test1; INSERT INTO test1 VALUES (5, 'testit'); GRANT SELECT (id) ON TABLE bug30269.test1 TO 'bug30269'@'localhost'; GRANT SELECT ON TABLE bug30269.view1 TO 'bug30269'@'localhost'; set global query_cache_size= 81920; USE bug30269; show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 0 # Select statement not stored in query cache because of column privileges. SELECT id FROM test1 WHERE id>2; id 5 show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 0 SELECT id FROM view1 WHERE id>2; id 5 show status like 'Qcache_queries_in_cache'; Variable_name Value Qcache_queries_in_cache 1 USE test; DROP DATABASE bug30269; DROP USER 'bug30269'@'localhost'; set GLOBAL query_cache_type=default; set GLOBAL query_cache_limit=default; set GLOBAL query_cache_min_res_unit=default; set GLOBAL query_cache_size=default;
Back to File Manager