Debian Codegraph @

mysql-test/suite/sys_vars/r/max_seeks_for_key_func.result

raw
1 DROP TABLE IF EXISTS t1;
2 CREATE TABLE t1
3 (a INT AUTO_INCREMENT PRIMARY KEY,
4 b CHAR(20)
5 );
6 SET @start_value= @@global.max_seeks_for_key;
7 '#--------------------FN_DYNVARS_084_01-------------------------#'
8 SELECT @@global.max_seeks_for_key = 10;
9 @@global.max_seeks_for_key = 10
10 0
11 SELECT @@session.max_seeks_for_key = 10;
12 @@session.max_seeks_for_key = 10
13 0
14 SET @@global.max_seeks_for_key = 20;
15 SELECT @@global.max_seeks_for_key;
16 @@global.max_seeks_for_key
17 20
18 INSERT INTO t1(b) VALUES("AREc");
19 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
20 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
21 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # # NULL
22 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # # Using where; Using join buffer (hash join)
23 Warnings:
24 Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
25 SET @@session.max_seeks_for_key = 2;
26 SELECT @@session.max_seeks_for_key;
27 @@session.max_seeks_for_key
28 2
29 INSERT INTO t1(b) VALUES("BREc");
30 INSERT INTO t1(b) VALUES("CRec");
31 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
32 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
33 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # # NULL
34 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # # Using where; Using join buffer (hash join)
35 Warnings:
36 Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
37 '#--------------------FN_DYNVARS_084_02-------------------------#'
38 SELECT @@global.max_seeks_for_key = 10;
39 @@global.max_seeks_for_key = 10
40 0
41 SELECT @@session.max_seeks_for_key = 10;
42 @@session.max_seeks_for_key = 10
43 0
44 SET @@global.max_seeks_for_key = 20;
45 SELECT @@global.max_seeks_for_key;
46 @@global.max_seeks_for_key
47 20
48 INSERT INTO t1(b) VALUES("AREc");
49 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
50 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
51 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # # NULL
52 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # # Using where; Using join buffer (hash join)
53 Warnings:
54 Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
55 SET @@session.max_seeks_for_key = 2;
56 SELECT @@session.max_seeks_for_key;
57 @@session.max_seeks_for_key
58 2
59 INSERT INTO t1(b) VALUES("BREc");
60 INSERT INTO t1(b) VALUES("CRec");
61 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
62 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
63 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # # NULL
64 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # # Using where; Using join buffer (hash join)
65 Warnings:
66 Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
67 INSERT INTO t1 VALUES(null,"test");
68 INSERT INTO t1 VALUES (null,"a"),(null,"a"),(null,"a"),
69 (null,"a"),(null,"a"),(null,"a"),(null,"a"),
70 (null,"a"),(null,"a"),(null,"a");
71 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
72 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
73 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # # NULL
74 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # # Using where; Using join buffer (hash join)
75 Warnings:
76 Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
77 ANALYZE TABLE t1;
78 Table Op Msg_type Msg_text
79 test.t1 analyze status OK
80 SET MAX_SEEKS_FOR_KEY=1;
81 EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
82 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
83 1 SIMPLE t1 NULL ALL NULL NULL NULL NULL # # NULL
84 1 SIMPLE t2 NULL ALL NULL NULL NULL NULL # # Using where; Using join buffer (hash join)
85 Warnings:
86 Note 1003 /* select#1 */ select straight_join `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b` from `test`.`t1` join `test`.`t1` `t2` where (`test`.`t2`.`b` = `test`.`t1`.`b`)
87 SET MAX_SEEKS_FOR_KEY=DEFAULT;
88 DROP TABLE t1;
89 SET @@global.max_seeks_for_key= @start_value;