Debian Codegraph @

mysql-test/suite/sys_vars/t/max_seeks_for_key_func.test

raw
1 # Test for max_seeks_for_key #
2
3 --disable_warnings
4 DROP TABLE IF EXISTS t1;
5 --enable_warnings
6
7
8 #########################
9 # Creating new table #
10 #########################
11
12 CREATE TABLE t1
13 (a INT AUTO_INCREMENT PRIMARY KEY,
14 b CHAR(20)
15 );
16
17 SET @start_value= @@global.max_seeks_for_key;
18
19 --echo '#--------------------FN_DYNVARS_084_01-------------------------#'
20 ##########################################################
21 # Test behavior of variable on new connection # 01 #
22 ##########################################################
23
24 CONNECT (test_con1,localhost,root,,);
25 CONNECTION test_con1;
26
27 # Value of session & global vairable here should be 10
28 SELECT @@global.max_seeks_for_key = 10;
29 SELECT @@session.max_seeks_for_key = 10;
30
31 # Setting global value of variable and inserting data in table
32 SET @@global.max_seeks_for_key = 20;
33 SELECT @@global.max_seeks_for_key;
34 INSERT INTO t1(b) VALUES("AREc");
35 --replace_column 10 # 11 #
36 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
37
38 # Setting session value of variable and inserting data in table
39 SET @@session.max_seeks_for_key = 2;
40 SELECT @@session.max_seeks_for_key;
41 INSERT INTO t1(b) VALUES("BREc");
42 INSERT INTO t1(b) VALUES("CRec");
43 --replace_column 10 # 11 #
44 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
45
46
47 --echo '#--------------------FN_DYNVARS_084_02-------------------------#'
48 ##########################################################
49 # Test behavior of variable on new connection # 02 #
50 ##########################################################
51
52
53 CONNECT (test_con2,localhost,root,,);
54 connection test_con2;
55
56
57 # Value of session & global vairable here should be 10
58 SELECT @@global.max_seeks_for_key = 10;
59 SELECT @@session.max_seeks_for_key = 10;
60
61 # Setting global value of variable and inserting data in table
62 SET @@global.max_seeks_for_key = 20;
63 SELECT @@global.max_seeks_for_key;
64 INSERT INTO t1(b) VALUES("AREc");
65 --replace_column 10 # 11 #
66 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
67
68 # Setting session value of variable and inserting data in table
69 SET @@session.max_seeks_for_key = 2;
70 SELECT @@session.max_seeks_for_key;
71 INSERT INTO t1(b) VALUES("BREc");
72 INSERT INTO t1(b) VALUES("CRec");
73 --replace_column 10 # 11 #
74 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
75
76 ######################################################
77 # Inserting values in table t and analyzing table #
78 ######################################################
79
80 INSERT INTO t1 VALUES(null,"test");
81 INSERT INTO t1 VALUES (null,"a"),(null,"a"),(null,"a"),
82 (null,"a"),(null,"a"),(null,"a"),(null,"a"),
83 (null,"a"),(null,"a"),(null,"a");
84 --replace_column 10 # 11 #
85 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
86 ANALYZE TABLE t1;
87
88 ###################################################################
89 # Setting new value for max_seeks_for_key and anaylyzing table #
90 ###################################################################
91
92 SET MAX_SEEKS_FOR_KEY=1;
93
94 --replace_column 10 # 11 #
95 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
96 SET MAX_SEEKS_FOR_KEY=DEFAULT;
97
98 connection default;
99 disconnect test_con1;
100 disconnect test_con2;
101
102 DROP TABLE t1;
103
104 SET @@global.max_seeks_for_key= @start_value;
105