索引类型:NORMAL 索引方法:BTREE
一般这样子设置就OK了
一.sql查询语句
SELECT
bp.id AS post_id,
`bp`.`title`,
bp.img AS bbs_img,
`bp`.`content`,
`bp`.`follow`,
`bp`.`reply`,
`bp`.`top`,
`bp`.`time`,
`bp`.`click`,
`u`.`nickname`,
`u`.`img`,
u.id AS uid,
bpf.id AS follow_id,
`bu`.`is_moderator`,
`bp`.`up`,
`bp`.`down`,
`pu`.`type`
FROM
`edu_bbs_post` `bp`
INNER JOIN `edu_user` `u` ON `u`.`id` = bp.add_user_id
AND u.deleted = 0
AND u.state = 0
LEFT JOIN `edu_bbs_post_follow` `bpf` ON `bpf`.`post_id` = bp.id
AND bpf.user_id = '103'
LEFT JOIN `edu_bbs_user` `bu` ON `bu`.`plate_id` = bp.plate_id
AND bu.user_id = '103'
AND bu.is_moderator = 1
AND bu.deleted = 0
LEFT JOIN `edu_bbs_post_updown` `pu` ON `pu`.`post_id` = bp.id
AND pu.user_id = '103'
WHERE
( bp.deleted = 0 AND bp.audit = 1 AND bp.plate_id = '4' )
ORDER BY
`bp`.`top` DESC,
`bp`.`id` DESC
LIMIT 0,
12;
1.2查看是否有索引 命令:DESC
二.添加索引 按照上面一个一个加
alter table `edu_bbs_post` add key plate(`plate_id`);
alter table `edu_bbs_post_follow` add key post_id(`post_id`);
alter table `edu_bbs_post_follow` add key user_id(`user_id`);
alter table `edu_bbs_post_follow` add key post_user(`post_id`,`user_id`);
alter table `edu_bbs_user` add key user_plate(`user_id`,`plate_id`);
alter table `edu_bbs_post_updown` add key post_user(`post_id`,`user_id`);
2.1 注意
plate(`plate_id`);
plate=索引名称
plate_id=要加索引的字段
2.2 联合索引 查询多个字段时,这样子写会自动判断用那个索引
alter table `edu_bbs_post_updown` add key post_user(`post_id`,`user_id`);