sql性能优化------索引不执行..

Question

explain select count(*) as number from
 (select CsMemberGallerys.id from cs_member_gallerys_inner as CsMemberGallerys,member_info as Member
 where  CsMemberGallerys.member_id = Member.id and  CsMemberGallerys.pic_count >0 and Member.status=54 and Member.deleted is null 
and CsMemberGallerys.cover_pic is not null and CsMemberGallerys.deleted is null order by CsMemberGallerys.click_count desc limit 200)tmp
结果如下...............................1 PRIMARY Select tables optimized away
2 DERIVED CsMemberGallerys ALL m_id_index,pic_count_index 98 Using filesort
2 DERIVED Member eq_ref PRIMARY,statu PRIMARY 4 nikon.CsMemberGallerys.member_id 1 Using where
-----------------
为什么此时不执行索引?我把CsMemberGallerys.pic_count >0 and Member.status=54 放到where 后面效果还是一样的...怎么不执行？

Answer 1

贴出以下信息。show index from cs_member_gallerys_inner;
show index from member_info;

Answer 2

show index from cs_member_gallerys_inner;cs_member_gallerys_inner 0 PRIMARY 1 id A 98 BTREE
cs_member_gallerys_inner 1 m_id_index 1 member_id A YES BTREE
cs_member_gallerys_inner 1 pic_count_index 1 pic_count A YES BTREE
show index from member_info;member_info 0 PRIMARY 1 id A 191 BTREE
member_info 1 y 1 email A 191 YES BTREE
member_info 1 suoyin1 1 city A 191 YES BTREE
member_info 1 statu 1 status A 7 YES BTREE

Answer 3

去掉SELECT COUNT(*)，再贴一下EXPLAIN的结果