Optimization for a particular query

[Gautam Parai]

Hi,

I have the following query and it is taking infinitely long to execute. I tried executing the subquery which executes in ~3 seconds. The number of rows generated by the subquery returns ~60000 rows which is then joined with other tables(millions of rows). I have never dealt with such huge databases earlier, so I am not very sure about query optimization. (The problem is in the portion of the query after UNION ALL. I have written the entire query I am trying to execute for the sake of completeness. Also, I had to obfuscate table & column names...sorry about that.)

SELECT C.tN, B.loID, A.lcID FROM A
INNER JOIN B ON B.oID = A.oID
INNER JOIN C ON A.cID = C.cID
LEFT JOIN (SELECT cID, SUM(count) as mCnt from C
INNER JOIN ME on C.tID = ME.tID
GROUP BY C.cID)
AS MT ON MT.cID = A.cID
WHERE (loID = 'XYZ' ) AND (MT.mCnt >= 1000) )

UNION ALL

(SELECT C.tN, B.loID, A.lcID FROM A
INNER JOIN (SELECT DISTINCT M.mlcID FROM A
INNER JOIN B ON B.oID = A.oID
INNER JOIN C ON A.cID = C.cID
LEFT JOIN (SELECT cID, SUM(count) as mCnt from C
INNER JOIN ME on C.tID = ME.tID
GROUP BY C.cID)
AS MT ON MT.cID = A.cID
INNER JOIN M ON A.lcID = M.lcID
WHERE (loID = 'XYZ' ) AND (MT.mCnt >= 1000) AND (M.mType = 'lmn' ) )
AS MT ON A.lcID = MT.mlcID
INNER JOIN B ON B.oID = A.oID
INNER JOIN C ON A.cID = C.cID
LEFT JOIN (SELECT cID, SUM(count) as mCnt from C
INNER JOIN ME on C.tID = ME.tID
GROUP BY C.cID)
AS MT ON MT.cID = A.cID
WHERE (MT.mCnt >= 1000)) LIMIT 0, 500

Regards,
Gautam