flush query cache; flush query cache; reset query cache; flush status; drop table if exists t1,t2,t3,t11,t21, mysqltest.t1; create table t1 (a int not null); insert into t1 values (1),(2),(3); select * from t1; a 1 2 3 select * from t1; a 1 2 3 select sql_no_cache * from t1; a 1 2 3 select length(now()) from t1; length(now()) 19 19 19 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 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 drop table t1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 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 (4),(5),(6); create table t3 (a int not null) type=MERGE UNION=(t1,t2) INSERT_METHOD=FIRST; select * from t3; a 1 2 3 4 5 6 select * from t3; a 1 2 3 4 5 6 show status like "Qcache_hits"; Variable_name Value Qcache_hits 2 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 insert into t2 values (7); show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 select * from t1; a 1 2 3 select * from t1; a 1 2 3 show status like "Qcache_hits"; Variable_name Value Qcache_hits 3 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 insert into t3 values (8); show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 select * from t3; a 1 2 3 8 4 5 6 7 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 update t2 set a=9 where a=7; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 select * from t1; a 1 2 3 8 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 update t3 set a=10 where a=1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 select * from t3; a 10 2 3 8 4 5 6 9 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 delete from t2 where a=9; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 select * from t1; a 10 2 3 8 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 delete from t3 where a=10; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1, t2, t3; 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; set query_cache_type=demand; create table t1 (a int not null); insert into t1 values (1),(2),(3); select * from t1; a 1 2 3 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 select sql_cache * from t1; a 1 2 3 set query_cache_type=2; select sql_cache * from t1; a 1 2 3 show status like "Qcache_hits"; Variable_name Value Qcache_hits 4 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 set query_cache_type=on; reset query cache; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 select sql_no_cache * from t1; a 1 2 3 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1; create table t1 (a text not null); select CONNECTION_ID() from t1; CONNECTION_ID() select FOUND_ROWS(); FOUND_ROWS() 0 select NOW() from t1; NOW() select CURDATE() from t1; CURDATE() select CURTIME() from t1; CURTIME() select DATABASE() from t1; DATABASE() select ENCRYPT("test") from t1; ENCRYPT("test") select LAST_INSERT_ID() from t1; last_insert_id() select RAND() from t1; RAND() select UNIX_TIMESTAMP() from t1; UNIX_TIMESTAMP() select USER() from t1; USER() select benchmark(1,1) from t1; benchmark(1,1) show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 create table t2 (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 t1 select * from t2; insert into t2 select * from t1; insert into t1 select * from t2; show status like "Qcache_hits"; Variable_name Value Qcache_hits 4 show status like "Qcache_lowmem_prunes"; Variable_name Value Qcache_lowmem_prunes 0 select a as a1, a as a2 from t1; select a as a2, a as a3 from t1; select a as a3, a as a4 from t1; select a as a1, a as a2 from t1; show status like "Qcache_hits"; Variable_name Value Qcache_hits 4 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 show status like "Qcache_lowmem_prunes"; Variable_name Value Qcache_lowmem_prunes 2 reset query cache; 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; select * from t1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1,t2; create database if not exists mysqltest; create table mysqltest.t1 (i int not null auto_increment, a int, primary key (i)); insert into mysqltest.t1 (a) values (1); select * from mysqltest.t1 where i is null; i a 1 1 select * from mysqltest.t1; i a 1 1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 drop database mysqltest; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 create table t1 (a char(1) not null); insert into t1 values("�"); select * from t1; a � set CHARACTER SET cp1251_koi8; select * from t1; a � set CHARACTER SET DEFAULT; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 show status like "Qcache_hits"; Variable_name Value Qcache_hits 4 drop table t1; create database if not exists mysqltest; create table mysqltest.t1 (i int not null); create table t1 (i int not null); insert into mysqltest.t1 (i) values (1); insert into t1 (i) values (2); select * from t1; i 2 use mysqltest; select * from t1; i 1 select * from t1; i 1 use test; select * from t1; i 2 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 show status like "Qcache_hits"; Variable_name Value Qcache_hits 6 drop database mysqltest; drop table t1; create table t1 (i int not null); insert into t1 (i) values (1),(2),(3),(4); select SQL_CALC_FOUND_ROWS * from t1 limit 2; i 1 2 select FOUND_ROWS(); FOUND_ROWS() 4 show status like "Qcache_hits"; Variable_name Value Qcache_hits 6 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 select * from t1 where i=1; i 1 select FOUND_ROWS(); FOUND_ROWS() 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 6 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 select SQL_CALC_FOUND_ROWS * from t1 limit 2; i 1 2 select FOUND_ROWS(); FOUND_ROWS() 4 show status like "Qcache_hits"; Variable_name Value Qcache_hits 7 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 select * from t1 where i=1; i 1 select FOUND_ROWS(); FOUND_ROWS() 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 8 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 drop table t1; flush query cache; reset query cache; create table t1 (a int not null); insert into t1 values (1),(2),(3); select * from t1; a 1 2 3 select * from t1; a 1 2 3 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 insert delayed into t1 values (4); select a from t1; a 1 2 3 4 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 drop table t1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 set GLOBAL query_cache_size=1000; show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 set GLOBAL query_cache_size=1100; set GLOBAL query_cache_size=1200; set GLOBAL query_cache_size=1300; set GLOBAL query_cache_size=1400; set GLOBAL query_cache_size=1500; set GLOBAL query_cache_size=1600; set GLOBAL query_cache_size=1700; set GLOBAL query_cache_size=1800; set GLOBAL query_cache_size=1900; show global variables like "query_cache_size"; Variable_name Value query_cache_size 1024 set GLOBAL query_cache_size=1048576; create table t1 (i int not null); create table t2 (i int not null); select * from t1; i show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 create temporary table t3 (i int not null); select * from t2; i show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 select * from t3; i show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 drop table t1, t2, t3; use mysql; select * from db; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 use test; select * from mysql.db; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0