mysql - 如何优化查询

Question

此查询返回登录用户的所有 friend ，这些 friend 出现在下面查询的“network”表中。这映射了这两个表中的 frd_id 和 mem_id。我优化了这个查询。但仍然需要时间才能得到结果。

请建议我针对相同的优化查询。

SELECT m.mem_id,m.profilenam,m.gender,m.photo_thumb,m.profile_type,n.frd_id as mem_id 
FROM `network` n 
left join members m on(n.frd_id=m.mem_id)
where ((n.frd_id = m.mem_id and n.mem_id=$uid) 
or (n.mem_id = m.mem_id and n.frd_id=$uid)) 
AND m.ban='n' 
AND m.deleted<>'Y' 
and profilenam like '%'

Answer 1

试试这个:

SELECT m.mem_id,m.profilenam,m.gender,m.photo_thumb,m.profile_type,m.mem_id 
FROM `network` n 
JOIN members m ON n.frd_id = m.mem_id
                  AND ( n.mem_id = $uid
                        OR ( n.mem_id = m.mem_id 
                             AND n.frd_id = $uid
                           )
                      )
WHERE m.profilenam IS NOT NULL
      AND m.ban = 'n' 
      AND m.deleted <> 'Y';