# This is to see how statements prepared via the PREPARE SQL command # go into the query cache: if using parameters they cannot; if not # using parameters they can. # Query cache is abbreviated as "QC" -- source include/have_query_cache.inc # embedded can't make more than one connection, which this test needs -- source include/not_embedded.inc connect (con1,127.0.0.1,root,,test,$MASTER_MYPORT,); connection default; set global query_cache_size=100000; flush status; create table t1(c1 int); insert into t1 values(1),(10),(100); # Prepared statements has no parameters, query caching should happen prepare stmt1 from "select * from t1 where c1=10"; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; # Another prepared statement (same text, same connection), should hit the QC prepare stmt2 from "select * from t1 where c1=10"; execute stmt2; show status like 'Qcache_hits'; execute stmt2; show status like 'Qcache_hits'; execute stmt2; show status like 'Qcache_hits'; # Another prepared statement (same text, other connection), should hit the QC connection con1; prepare stmt3 from "select * from t1 where c1=10"; execute stmt3; show status like 'Qcache_hits'; execute stmt3; show status like 'Qcache_hits'; execute stmt3; show status like 'Qcache_hits'; connection default; # A non-prepared statement (same text, same connection), should hit # the QC (as it uses the text protocol like SQL EXECUTE). # But if it uses the binary protocol, it will not hit. So we make sure # that it uses the text protocol: -- disable_ps_protocol select * from t1 where c1=10; show status like 'Qcache_hits'; # A non-prepared statement (same text, other connection), should hit # the QC. To test that it hits the result of SQL EXECUTE, we need to # empty/repopulate the QC (to remove the result from the non-prepared # SELECT just above). flush tables; execute stmt1; show status like 'Qcache_hits'; connection con1; select * from t1 where c1=10; show status like 'Qcache_hits'; -- enable_ps_protocol connection default; # Prepared statement has parameters, query caching should not happen prepare stmt1 from "select * from t1 where c1=?"; show status like 'Qcache_hits'; set @a=1; execute stmt1 using @a; show status like 'Qcache_hits'; set @a=100; execute stmt1 using @a; show status like 'Qcache_hits'; set @a=10; execute stmt1 using @a; show status like 'Qcache_hits'; # See if enabling/disabling the query cache between PREPARE and # EXECUTE is an issue; the expected result is that the query cache # will not be used. # Indeed, decision to read/write the query cache is taken at PREPARE # time, so if the query cache was disabled at PREPARE time then no # execution of the statement will read/write the query cache. # If the query cache was enabled at PREPARE time, but disabled at # EXECUTE time, at EXECUTE time the query cache internal functions do # nothing so again the query cache is not read/written. But if the # query cache is re-enabled before another execution then that # execution will read/write the query cache. # QC is enabled at PREPARE prepare stmt1 from "select * from t1 where c1=10"; # then QC is disabled at EXECUTE set global query_cache_size=0; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; # then QC is re-enabled for more EXECUTE. set global query_cache_size=100000; # Note that this execution will not hit results from the # beginning of the test (because QC has been emptied meanwhile by # setting its size to 0). execute stmt1; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; # QC is disabled at PREPARE set global query_cache_size=0; prepare stmt1 from "select * from t1 where c1=10"; # then QC is enabled at EXECUTE set global query_cache_size=100000; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; execute stmt1; show status like 'Qcache_hits'; # QC is disabled at PREPARE set global query_cache_size=0; prepare stmt1 from "select * from t1 where c1=?"; # then QC is enabled at EXECUTE set global query_cache_size=100000; show status like 'Qcache_hits'; set @a=1; execute stmt1 using @a; show status like 'Qcache_hits'; set @a=100; execute stmt1 using @a; show status like 'Qcache_hits'; set @a=10; execute stmt1 using @a; show status like 'Qcache_hits'; drop table t1; set global query_cache_size=0; flush status; # reset Qcache status variables for next tests