I have a pretty complex mysql query and I'm trying to understand if there's a way to make it use indexes. The query looks like:
我有一个非常复杂的mysql查询,我试图了解是否有办法让它使用索引。查询看起来像:
SELECT t1.a, t1.b, t2.c, COUNT(t1.li), SUM(t1.tc)
FROM t1 LEFT JOIN t2
ON (t1.d=t2.d AND t1.e=t2.e)
WHERE t1.pt="XXX" GROUP BY t1.a, t1.b, t2.c
HAVING t1.li > 0 AND t1.tc > 4;
I am only able to get it optimized using an index on t1.pt (or the "WHERE" clause) but can't get the GROUP BY part go faster.
我只能使用t1.pt(或“WHERE”子句)上的索引对其进行优化,但无法使GROUP BY部分更快。
Is there a way to make GROUP BY use indexes when grouping is on columns from two tables?
当分组在两个表的列上时,有没有办法使GROUP BY使用索引?
1 个解决方案
#1
1
First of all you should add indexes to joined field of both tables:
首先,您应该为两个表的连接字段添加索引:
t1.d, t2.d, t1.e, t2.e
then add indexes to group by fields
然后将索引添加到按字段分组
t1.a, t1.b, t2.c
Also in the HAVING part I think you should write:
同样在HAVING部分,我认为你应该写:
HAVING count(t1.li) > 0 AND sum(t1.tc) > 4;
#1
1
First of all you should add indexes to joined field of both tables:
首先,您应该为两个表的连接字段添加索引:
t1.d, t2.d, t1.e, t2.e
then add indexes to group by fields
然后将索引添加到按字段分组
t1.a, t1.b, t2.c
Also in the HAVING part I think you should write:
同样在HAVING部分,我认为你应该写:
HAVING count(t1.li) > 0 AND sum(t1.tc) > 4;