4
#Test for max_seeks_for_key#
8
drop table if exists t1;
12
#########################
13
# Creating new table #
14
#########################
17
(a int auto_increment primary key,
23
--echo '#--------------------FN_DYNVARS_084_01-------------------------#'
24
##########################################################
25
# Test behavior of variable on new connection # 01 #
26
##########################################################
28
CONNECT (test_con1,localhost,root,,);
31
# Value of session & global vairable here should be 10
32
SELECT @@global.max_seeks_for_key = 10;
33
SELECT @@session.max_seeks_for_key = 10;
35
# Setting global value of variable and inserting data in table
36
SET @@global.max_seeks_for_key = 20;
37
SELECT @@global.max_seeks_for_key;
38
INSERT into t1(b) values("AREc");
39
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
41
# Setting session value of variable and inserting data in table
42
SET @@session.max_seeks_for_key = 2;
43
SELECT @@session.max_seeks_for_key;
44
INSERT into t1(b) values("BREc");
45
INSERT into t1(b) values("CRec");
46
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
49
--echo '#--------------------FN_DYNVARS_084_02-------------------------#'
50
##########################################################
51
# Test behavior of variable on new connection # 02 #
52
##########################################################
55
CONNECT (test_con2,localhost,root,,);
59
# Value of session & global vairable here should be 10
60
SELECT @@global.max_seeks_for_key = 10;
61
SELECT @@session.max_seeks_for_key = 10;
63
# Setting global value of variable and inserting data in table
64
SET @@global.max_seeks_for_key = 20;
65
SELECT @@global.max_seeks_for_key;
66
INSERT into t1(b) values("AREc");
67
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
69
# Setting session value of variable and inserting data in table
70
SET @@session.max_seeks_for_key = 2;
71
SELECT @@session.max_seeks_for_key;
72
INSERT into t1(b) values("BREc");
73
INSERT into t1(b) values("CRec");
74
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
81
#####################################################
82
# Inserting values in table t and analyzing table #
83
#####################################################
85
INSERT INTO t1 values(null,"test");
86
INSERT INTO t1 VALUES (null,"a"),(null,"a"),(null,"a"),
87
(null,"a"),(null,"a"),(null,"a"),(null,"a"),
88
(null,"a"),(null,"a"),(null,"a");
89
explain SELECT STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
92
###################################################################
93
# Setting new value for max_seeks_for_key and anaylyzing table #
94
###################################################################
96
SET MAX_SEEKS_FOR_KEY=1;
99
explain select STRAIGHT_JOIN * from t1,t1 as t2 where t1.b=t2.b;
100
SET MAX_SEEKS_FOR_KEY=DEFAULT;