SELECT
s1.*,
(SELECT SUM(pl_points) FROM member_points WHERE points < 0 AND p_memberid=s1.member_id) AS history_used_point
FROM member s1
GROUP BY s1.member_id;
一个查询会员的历史积分使用明细的sql,在member_points(积分)表中,使用积分的话保存到数据库points是负数,新增积分保存到数据库points是正数,现在查询会员信息的同时关联查询积分表信息,加上 ** p_memberid=s1.member_id ** 之后查询变得非常慢,这个sql该如何优化。points索引加上也没起到作用
优化后:
select s1.*,s2.history_user_point
from member s1
left join
(select p_memberid,sum(pl_points) as history_used_point from member_points where points<0 group by p_memberid) s2 on s1.memberid=s2.p_memberid
优化:
原来在navicate客户端添加索引,但是却没加进去,使用 show index 发现没有加上,通过sql再重新添加一次索引,将member_points 中的points加上索引~~~~~~~