EXPLAIN SELECT MEMBER.nick_name AS nick_name,MEMBER.head_pic AS head_pic,FRIEND.*
FROM cs_member_friends_inner AS FRIEND
LEFT JOIN member_info AS MEMBER ON MEMBER.id=FRIEND.friend_id
WHERE MEMBER.deleted IS NULL AND MEMBER.status=54 AND FRIEND.deleted IS NULL AND FRIEND.member_id = 12354
ORDER BY FRIEND.created DESC -------------id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE FRIEND ALL \N \N \N \N 136 Using where; Using filesort
1 SIMPLE MEMBER eq_ref PRIMARY PRIMARY 4 nikon.FRIEND.friend_id 1 Using where
-----------------
其中member这个表用到了主键索引,这个改如何优化优化....
FROM cs_member_friends_inner AS FRIEND
LEFT JOIN member_info AS MEMBER ON MEMBER.id=FRIEND.friend_id
WHERE MEMBER.deleted IS NULL AND MEMBER.status=54 AND FRIEND.deleted IS NULL AND FRIEND.member_id = 12354
ORDER BY FRIEND.created DESC -------------id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE FRIEND ALL \N \N \N \N 136 Using where; Using filesort
1 SIMPLE MEMBER eq_ref PRIMARY PRIMARY 4 nikon.FRIEND.friend_id 1 Using where
-----------------
其中member这个表用到了主键索引,这个改如何优化优化....
MEMBER.status
加索引 强制执行
create index in_2 on cs_member_friends_inner(member_id);try~
2:member这个表用到了主键索引,主键索引效率更高。
CREATE INDEX friend_id_index ON cs_member_friends_inner(friend_id)----这个是建立的索引,
EXPLAIN SELECT SQL_NO_CACHE MEMBER.nick_name AS nick_name,MEMBER.head_pic AS head_pic,FRIEND.*
FROM member_info AS MEMBER
LEFT JOIN cs_member_friends_inner AS FRIEND
ON FRIEND.friend_id=MEMBER.id
WHERE MEMBER.deleted IS NULL AND MEMBER.status=54 AND FRIEND.deleted IS NULL AND FRIEND.member_id = 12354
ORDER BY FRIEND.created DESC ----
执行结果是:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE FRIEND ALL friend_id_index \N \N \N 136 Using where; Using filesort
1 SIMPLE MEMBER eq_ref PRIMARY PRIMARY 4 nikon.FRIEND.friend_id 1 Using where怎么没有使用我建立的索引啊,
-----------
EXPLAIN SELECT SQL_NO_CACHE MEMBER.nick_name AS nick_name,MEMBER.head_pic AS head_pic,FRIEND.*
FROM member_info AS MEMBER
LEFT JOIN cs_member_friends_inner AS FRIEND FORCE INDEX(friend_id)
ON FRIEND.friend_id=MEMBER.id
WHERE MEMBER.deleted IS NULL AND MEMBER.status=54 AND FRIEND.deleted IS NULL AND FRIEND.member_id = 12354
ORDER BY FRIEND.created DESC
强制使用索引结果:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE FRIEND ALL friend_id_index \N \N \N 136 Using where; Using filesort
1 SIMPLE MEMBER eq_ref PRIMARY PRIMARY 4 nikon.FRIEND.friend_id 1 Using where
把你的数据库打包发给我试试[email protected]
FROM member_info AS Member FORCE INDEX(name_index) WHERE Member.nick_name LIKE '%dd%'
AND Member.deleted IS NULL AND Member.status=54 AND Member.head_frist <> NULL
ORDER BY Member.head_frist DESC,Member.created DESC LIMIT 0,10
-------------强制使用索引,但是结果如下:(显示并没有使用我建立的索引)
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Member ALL \N \N \N \N 192 Using where; Using filesort---------
但是我将like后面的第一个%去掉结果使用了索引:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE Member range name_index name_index 63 \N 1 Using where; Using filesort
在你的SQL语句里没有看到LIKE 啊。
FROM member_info AS Member FORCE INDEX(name_index) WHERE Member.nick_name LIKE '%dd%'
AND Member.deleted IS NULL AND Member.status=54 AND Member.head_frist <> NULL
ORDER BY Member.head_frist DESC,Member.created DESC LIMIT 0,10