遇到的问题跟我一样了,索引肯定是要建立的(否则要几十妙的); in 改为exits (select .....from ...where...),两个都要,否则有索引也白搭,我的数据才3,000,000 用in 根本就出不来,用exits 几秒就出不来了当然我是分页的了。
查询语句改为 SELECT end_date,SUM(values) FROM perf, (SELECT distinct memberid FROM groupmember WHERE groupid = 1) src, (SELECT distinct memberid FROM groupmember WHERE groupid = 2) dst WHERE member_src=src.memberid AND member_dst=dst.memberid GROUP BY end_date;groupmember.groupid 建索引 perf.member_src,perf.member_dst建复合索引
SELECT end_date,SUM(values) FROM perf a WHERE exists(SELECT 1 FROM groupmember WHERE a.member_src=memberid and groupid = 1) AND exists(SELECT 1 FROM groupmember WHERE a.member_dst=memberid and groupid = 2) GROUP BY end_date;
in子句怎么改为关联?
in 改为exits (select .....from ...where...),两个都要,否则有索引也白搭,我的数据才3,000,000 用in 根本就出不来,用exits 几秒就出不来了当然我是分页的了。
SELECT end_date,SUM(values) FROM perf,
(SELECT distinct memberid FROM groupmember WHERE groupid = 1) src,
(SELECT distinct memberid FROM groupmember WHERE groupid = 2) dst
WHERE member_src=src.memberid AND member_dst=dst.memberid
GROUP BY end_date;groupmember.groupid 建索引
perf.member_src,perf.member_dst建复合索引