索引优化——单表优化

优化目的是：

将type 类型优化为ref,

CREATE TABLE IF NOT EXISTS `runoob`.`aritcle` (
   `id` INT(11) NOT NULL PRIMARY KEY auto_increment,
  `author_id` INT(11) NOT NULL,
  `category_id` INT(11) NOT NULL,
  `views` INT(11) NOT NULL,
  `comments` INT(11) NULL DEFAULT NULL,
  `title` varchar(11) NULL,
  `content` varchar(11) NULL
  )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8

use runoob

insert into aritcle(`author_id`,`category_id`,  `views`,`comments`, `title`, `content`)
values (1,1,1,1,'1','1');

insert into aritcle(`author_id`,`category_id`,  `views`,`comments`, `title`, `content`)
values (2,2,2,2,'2','2');

insert into aritcle(`author_id`,`category_id`,  `views`,`comments`, `title`, `content`)
values (1,1,3,3,'3','3');


select *  from aritcle



explain
 select 
id,author_id
from aritcle
where category_id=1
and comments>1
order by views
desc limit 1;


create index idx_atticle_ccv  on aritcle(author_id,comments,views)

show index from aritcle

explain
 select 
id,author_id
from aritcle
where category_id=1
and comments=3
order by views
desc limit 1;

drop index idx_atticle_ccv on aritcle



create index idx_atticle_ccv  on aritcle(category_id,views)
show index from aritcle

explain
 select 
id,author_id
from aritcle
where category_id=1
and comments>1
order by views
desc limit 1;