DROP TABLE IF EXISTS t1;
CREATE TABLE t1
(a INT AUTO_INCREMENT PRIMARY KEY,
b CHAR(20)
);
SET @start_value= @@global.max_seeks_for_key;
'#--------------------FN_DYNVARS_084_01-------------------------#'
SELECT @@global.max_seeks_for_key = 10;
@@global.max_seeks_for_key = 10
0
SELECT @@session.max_seeks_for_key = 10;
@@session.max_seeks_for_key = 10
0
SET @@global.max_seeks_for_key = 20;
SELECT @@global.max_seeks_for_key;
@@global.max_seeks_for_key
20
INSERT INTO t1(b) VALUES("AREc");
EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	NULL
1	SIMPLE	t2	system	NULL	NULL	NULL	NULL	1	NULL
SET @@session.max_seeks_for_key = 2;
SELECT @@session.max_seeks_for_key;
@@session.max_seeks_for_key
2
INSERT INTO t1(b) VALUES("BREc");
INSERT INTO t1(b) VALUES("CRec");
EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	NULL
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	Using where; Using join buffer (Block Nested Loop)
'#--------------------FN_DYNVARS_084_02-------------------------#'
SELECT @@global.max_seeks_for_key = 10;
@@global.max_seeks_for_key = 10
0
SELECT @@session.max_seeks_for_key = 10;
@@session.max_seeks_for_key = 10
0
SET @@global.max_seeks_for_key = 20;
SELECT @@global.max_seeks_for_key;
@@global.max_seeks_for_key
20
INSERT INTO t1(b) VALUES("AREc");
EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	4	NULL
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	4	Using where; Using join buffer (Block Nested Loop)
SET @@session.max_seeks_for_key = 2;
SELECT @@session.max_seeks_for_key;
@@session.max_seeks_for_key
2
INSERT INTO t1(b) VALUES("BREc");
INSERT INTO t1(b) VALUES("CRec");
EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	NULL
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (Block Nested Loop)
INSERT INTO t1 VALUES(null,"test");
INSERT INTO t1 VALUES (null,"a"),(null,"a"),(null,"a"),
(null,"a"),(null,"a"),(null,"a"),(null,"a"),
(null,"a"),(null,"a"),(null,"a");
EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	17	NULL
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	17	Using where; Using join buffer (Block Nested Loop)
ANALYZE TABLE t1;
Table	Op	Msg_type	Msg_text
test.t1	analyze	status	OK
SET MAX_SEEKS_FOR_KEY=1;
EXPLAIN SELECT STRAIGHT_JOIN * FROM t1,t1 AS t2 WHERE t1.b = t2.b;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	17	NULL
1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	17	Using where; Using join buffer (Block Nested Loop)
SET MAX_SEEKS_FOR_KEY=DEFAULT;
DROP TABLE t1;
SET @@global.max_seeks_for_key= @start_value;