CODEWITHSUNDEEP
mysqlindexinggroup-byleft-join
nickb
nickb

Reputation: 924

Optimizing MySQL query with: join, where, group by, having

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:

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.

Is there a way to make GROUP BY use indexes when grouping is on columns from two tables?

Upvotes: 0

Views: 112

Answers (1)

valex
valex

Reputation: 24144

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 count(t1.li) > 0 AND sum(t1.tc) > 4;

Upvotes: 1

Related Questions