flush query cache; flush query cache; reset query cache; flush status; drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t257,t00,mysqltest.t1; drop database if exists mysqltest; 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 union select * from t1; a 1 2 3 set query_cache_type=2; select sql_cache * from t1 union select * from t1; a 1 2 3 select * from t1 union 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 2 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 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 create table t1 (a int); set GLOBAL query_cache_size=1000; show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 select * from t1; a set GLOBAL query_cache_size=1024; show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 select * from t1; a set GLOBAL query_cache_size=10240; show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 select * from t1; a set GLOBAL query_cache_size=20480; show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 select * from t1; a set GLOBAL query_cache_size=40960; show global variables like "query_cache_size"; Variable_name Value query_cache_size 0 select * from t1; a set GLOBAL query_cache_size=51200; show global variables like "query_cache_size"; Variable_name Value query_cache_size 51200 select * from t1; a set GLOBAL query_cache_size=61440; show global variables like "query_cache_size"; Variable_name Value query_cache_size 61440 select * from t1; a set GLOBAL query_cache_size=81920; show global variables like "query_cache_size"; Variable_name Value query_cache_size 81920 select * from t1; a set GLOBAL query_cache_size=102400; show global variables like "query_cache_size"; Variable_name Value query_cache_size 102400 select * from t1; a drop table t1; 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 create table t1(id int auto_increment primary key); insert into t1 values (NULL), (NULL), (NULL); select * from t1 where id=2; id 2 alter table t1 rename to t2; select * from t1 where id=2; Table 'test.t1' doesn't exist drop table t2; select * from t1 where id=2; Table 'test.t1' doesn't exist create table t1 (word char(20) not null); select * from t1; word show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 load data infile '../../std_data/words.dat' into table t1; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1; drop table if exists t1; create table t1 (a int); insert into t1 values (1),(2),(3); show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 select * from t1 into outfile "query_caceh.out.file"; select * from t1 limit 1 into dumpfile "query_cache.dump.file"; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1; create table t1 (a int); insert into t1 values (1),(2); show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 select * from t1; a 1 2 SET OPTION SQL_SELECT_LIMIT=1; select * from t1; a 1 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 2 SET OPTION SQL_SELECT_LIMIT=DEFAULT; drop table t1; flush status; select count(*) from t00; count(*) 514 select count(*) from t00; count(*) 514 show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 1 show status like "Qcache_hits"; Variable_name Value Qcache_hits 1 delete from t256; show status like "Qcache_queries_in_cache"; Variable_name Value Qcache_queries_in_cache 0 drop table t1,t2,t3,t4,t5,t6,t7,t8,t9,t10,t11,t12,t13,t14,t15,t16,t17,t18,t19,t20,t21,t22,t23,t24,t25,t26,t27,t28,t29,t30,t31,t32,t33,t34,t35,t36,t37,t38,t39,t40,t41,t42,t43,t44,t45,t46,t47,t48,t49,t50,t51,t52,t53,t54,t55,t56,t57,t58,t59,t60,t61,t62,t63,t64,t65,t66,t67,t68,t69,t70,t71,t72,t73,t74,t75,t76,t77,t78,t79,t80,t81,t82,t83,t84,t85,t86,t87,t88,t89,t90,t91,t92,t93,t94,t95,t96,t97,t98,t99,t100,t101,t102,t103,t104,t105,t106,t107,t108,t109,t110,t111,t112,t113,t114,t115,t116,t117,t118,t119,t120,t121,t122,t123,t124,t125,t126,t127,t128,t129,t130,t131,t132,t133,t134,t135,t136,t137,t138,t139,t140,t141,t142,t143,t144,t145,t146,t147,t148,t149,t150,t151,t152,t153,t154,t155,t156,t157,t158,t159,t160,t161,t162,t163,t164,t165,t166,t167,t168,t169,t170,t171,t172,t173,t174,t175,t176,t177,t178,t179,t180,t181,t182,t183,t184,t185,t186,t187,t188,t189,t190,t191,t192,t193,t194,t195,t196,t197,t198,t199,t200,t201,t202,t203,t204,t205,t206,t207,t208,t209,t210,t211,t212,t213,t214,t215,t216,t217,t218,t219,t220,t221,t222,t223,t224,t225,t226,t227,t228,t229,t230,t231,t232,t233,t234,t235,t236,t237,t238,t239,t240,t241,t242,t243,t244,t245,t246,t247,t248,t249,t250,t251,t252,t253,t254,t255,t256,t257,t00;