Preparation data

create table if not exists `class`(
`id` int(10) unsigned not null auto_increment,
`card` varchar(32) not null,
primary key(`id`)
);

create table if not exists `book`(
`bookid` int(10) unsigned not null auto_increment,
`card` varchar(32) not null,
primary key(`bookid`)
);


insert into class(card) values('Philosophy');
insert into class(card) values('Religion');
insert into class(card) values('Social Sciences');
insert into class(card) values('Politics');
insert into class(card) values('Law');
insert into class(card) values('Military');
insert into class(card) values('Economics');
insert into class(card) values('Culture');
insert into class(card) values('science');
insert into class(card) values('education');
insert into class(card) values('Sports');
insert into class(card) values('language');
insert into class(card) values('Written words');
insert into class(card) values('literature');
insert into class(card) values('Art');
insert into class(card) values('History');
insert into class(card) values('Geography');
insert into class(card) values('natural science');
insert into class(card) values('Mathematics');
insert into class(card) values('Chemistry');

insert into book(card) values('Philosophy');
insert into book(card) values('Religion');
insert into book(card) values('Politics');
insert into book(card) values('Economics');
insert into book(card) values('Chemistry');
insert into book(card) values('natural science');
insert into book(card) values('Culture');
insert into book(card) values('Law');
insert into book(card) values('History');
insert into book(card) values('Religion');
insert into book(card) values('Philosophy');
insert into book(card) values('Social Sciences');
insert into book(card) values('science');
insert into book(card) values('Politics');
insert into book(card) values('Military');
insert into book(card) values('Sports');
insert into book(card) values('Social Sciences');
insert into book(card) values('Law');
insert into book(card) values('language');
insert into book(card) values('Philosophy');

• Double table

explain select * from class left join book on class.card = book.card;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL | NULL    | NULL |   20 | NULL                                               |
|  1 | SIMPLE      | book  | ALL  | NULL          | NULL | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+

Analysis: Both tables have all type s, which is very bad, rows are 20 (equal to the total number of records), which also shows this.

create index idx_class_card on class(card);
explain select * from class left join book on class.card = book.card;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | class | index | NULL          | idx_class_card | 98      | NULL |   20 | Using index                                        |
|  1 | SIMPLE      | book  | ALL   | NULL          | NULL           | NULL    | NULL |   20 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+----------------------------------------------------+

Analysis:

1) To create an index on the left outer join table, the type of table class is changed from all to index, which means that the efficiency is improved, but only from scanned data files to scanned index trees, which is essentially still a "full table scan". At the same time, it shows that the index is indeed effective, and the key value also shows this point.

2) key_len is 98, and card is varchar(32), and the internal encoding is utf8, each character is 3 bytes, so the index length is 32 * 3 + 2 = 98 bytes, which indicates that the index is fully utilized.

3) rows are still two 20 rows, indicating that they are still full-table scans.

drop index idx_class_card on class;
create index idx_book_card on book(card);
explain select * from class left join book on class.card = book.card;
+----+-------------+-------+------+---------------+---------------+---------+-----------------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref             | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-----------------+------+-------------+
|  1 | SIMPLE      | class | ALL  | NULL          | NULL          | NULL    | NULL            |   20 | NULL        |
|  1 | SIMPLE      | book  | ref  | idx_book_card | idx_book_card | 98      | db01.class.card |    1 | Using index |
+----+-------------+-------+------+---------------+---------------+---------+-----------------+------+-------------+

Analysis:

1) Destroy the previous index, create an index on the right table of left outer join, and change the type of the table book to Ref. This means that the efficiency is greatly improved. The index is used to query and match. Because the idx_book_card of book is only a common index and does not have uniqueness, it is not eq_ref.

2) the number of lines becomes 20 and 1, which means that the number of check lines is also very low.