1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

CREATE TABLE `school` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_code` varchar(255) DEFAULT NULL,
  `school_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `code_index` (`school_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_code` varchar(255) DEFAULT NULL,
  `student_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `code_index` (`school_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

delimiter  //
CREATE PROCEDURE init_school() 
BEGIN 
	DECLARE schid INT;
	SET schid = 0;
	START TRANSACTION; 
	WHILE schid < 100 DO
		insert into school(school_code,school_name) values (concat('code',schid),concat('name',schid));
		SET schid = schid + 1;
	END WHILE;
	COMMIT; 
END //
delimiter ;

delimiter  //
CREATE PROCEDURE init_student() 
BEGIN 
	DECLARE schid INT;
	DECLARE stuid INT;
	SET schid = 0;
	WHILE schid < 100 DO
		SET stuid = 0;
		START TRANSACTION; 
		WHILE stuid < 50 DO
			insert into student(school_code,student_name) values (concat('code',schid),concat('stu_name',stuid));
			SET stuid = stuid + 1;
		END WHILE;
		SET schid = schid + 1;
		COMMIT; 
	END WHILE;
END //
delimiter ;

call init_school();
call init_student();

1
2
3
4
5
6
7
8

explain select * from school s1,student s2 where s1.school_code = s2.school_code and s2.school_code = 'code10';

+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                                              |
+
|  1 | SIMPLE      | s2    | NULL       | ref  | code_index    | code_index | 1023    | const |   50 |      100 | NULL                                               |
|  1 | SIMPLE      | s1    | NULL       | ALL  | NULL          | NULL       | NULL    | NULL  |  100 |      100 | Using where; Using join buffer (Block Nested Loop) |
+

1
2
3
4
5
6
7
8

explain select * from school s1,student s2 where s1.school_code = s2.school_code and s2.school_code = 'code10';

+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra                 |
+
|  1 | SIMPLE      | s1    | NULL       | ref  | code_index    | code_index | 1023    | const |    1 |      100 | NULL                  |
|  1 | SIMPLE      | s2    | NULL       | ref  | code_index    | code_index | 1023    | const |   50 |      100 | Using index condition |
+