1set @@global.optimizer_switch=@@local.optimizer_switch; 2set @@global.join_cache_level=@@local.join_cache_level; 3DROP TABLE IF EXISTS t1; 4CREATE TABLE t1 5(a INT AUTO_INCREMENT PRIMARY KEY, 6b CHAR(20) 7); 8SET @start_value= @@global.max_seeks_for_key; 9'#--------------------FN_DYNVARS_084_01-------------------------#' 10CONNECT test_con1,localhost,root,,; 11connection test_con1; 12SET @@global.max_seeks_for_key = 20; 13SELECT @@global.max_seeks_for_key; 14@@global.max_seeks_for_key 1520 16INSERT INTO t1(b) VALUES("AREc"); 17EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; 18id select_type table type possible_keys key key_len ref rows Extra 191 SIMPLE t1 system NULL NULL NULL NULL 1 201 SIMPLE t2 system NULL NULL NULL NULL 1 21SET @@session.max_seeks_for_key = 2; 22SELECT @@session.max_seeks_for_key; 23@@session.max_seeks_for_key 242 25INSERT INTO t1(b) VALUES("BREc"); 26INSERT INTO t1(b) VALUES("CRec"); 27EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; 28id select_type table type possible_keys key key_len ref rows Extra 291 SIMPLE t1 ALL NULL NULL NULL NULL 3 301 SIMPLE t2 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join) 31'#--------------------FN_DYNVARS_084_02-------------------------#' 32CONNECT test_con2,localhost,root,,; 33connection test_con2; 34SELECT @@global.max_seeks_for_key = 10; 35@@global.max_seeks_for_key = 10 360 37SELECT @@session.max_seeks_for_key = 10; 38@@session.max_seeks_for_key = 10 390 40SET @@global.max_seeks_for_key = 20; 41SELECT @@global.max_seeks_for_key; 42@@global.max_seeks_for_key 4320 44INSERT INTO t1(b) VALUES("AREc"); 45EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; 46id select_type table type possible_keys key key_len ref rows Extra 471 SIMPLE t1 ALL NULL NULL NULL NULL 4 481 SIMPLE t2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (flat, BNL join) 49SET @@session.max_seeks_for_key = 2; 50SELECT @@session.max_seeks_for_key; 51@@session.max_seeks_for_key 522 53INSERT INTO t1(b) VALUES("BREc"); 54INSERT INTO t1(b) VALUES("CRec"); 55EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; 56id select_type table type possible_keys key key_len ref rows Extra 571 SIMPLE t1 ALL NULL NULL NULL NULL 6 581 SIMPLE t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) 59INSERT INTO t1 VALUES(null,"test"); 60INSERT INTO t1 VALUES (null,"a"),(null,"a"),(null,"a"), 61(null,"a"),(null,"a"),(null,"a"),(null,"a"), 62(null,"a"),(null,"a"),(null,"a"); 63EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; 64id select_type table type possible_keys key key_len ref rows Extra 651 SIMPLE t1 ALL NULL NULL NULL NULL 17 661 SIMPLE t2 ALL NULL NULL NULL NULL 17 Using where; Using join buffer (flat, BNL join) 67ANALYZE TABLE t1; 68Table Op Msg_type Msg_text 69test.t1 analyze status Engine-independent statistics collected 70test.t1 analyze status OK 71SET MAX_SEEKS_FOR_KEY=1; 72EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b; 73id select_type table type possible_keys key key_len ref rows Extra 741 SIMPLE t1 ALL NULL NULL NULL NULL 17 751 SIMPLE t2 ALL NULL NULL NULL NULL 17 Using where; Using join buffer (flat, BNL join) 76SET MAX_SEEKS_FOR_KEY=DEFAULT; 77connection default; 78disconnect test_con1; 79disconnect test_con2; 80DROP TABLE t1; 81SET @@global.max_seeks_for_key= @start_value; 82SET @@global.optimizer_switch= @save_optimizer_switch; 83SET @@global.join_cache_level= �@save_join_cache_level; 84