MySql（十五）--索引两表优化

create table if not exists `class`(
 `id` INT(10) unsigned NOT NULL auto_increment,
 `card` INT(10) unsigned NOT NULL,
 primary key(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

create table if not exists `book`(
 `bookid` INT(10) unsigned NOT NULL  auto_increment,
 `card` INT(10) unsigned NOT NULL,
 primary key(`bookid`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));
insert into class(card) values(floor(1+(RAND()*20)));

insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));
insert into book(card) values(floor(1+(RAND()*20)));

添加索引优化

alter table book add index y (card)

drop index y on book;

alter table class add index y (card)