how to solve this complex query
Posted by:
aftab ali
Date: January 18, 2009 11:28PM
hi,
below is the query which i have written but it's too slow....
please any can help me how can i improve the speed of this query...
INDEX is created on PARTITIONIDTIME(`PARTITIONID`,'CONNECTTIME`);
it's urgent.
******************************************************************
SELECT C.ACTIVE AS ACTIVE, C.TOTAL AS TOTAL, C.COMPLETED AS COMPLETED, C.ALERTDELAY AS ALERTDELAY, C.DURATION AS DURATION, C.BILLDURATION AS BILLBURATION, C.MAXBILLDURATION AS MAXBILLDURATION, C.BUYCHARGE AS BUYCHARGE, C.SELLCHARGE AS SELLCHARGE, C.BUYMARGIN AS BUYMARGIN, C.SELLMARGIN AS SELLMARGIN, C.BUYFEE AS BUYFEE,C.SELLFEE AS SELLFEE, C.TYPE AS TYPE,G.NAME AS ENTITYTYPE, '' AS SUBENTITYTYPE, C.ID AS ID
FROM (
SELECT A.GROUPID AS ID,A.TYPE AS TYPE, A.TOTAL AS TOTAL, A.ACTIVE AS ACTIVE, A.COMPLETED AS COMPLETED, A.ALERTDELAY AS ALERTDELAY, A.DURATION AS DURATION, A.BILLDURATION AS BILLDURATION, A.MAXBILLDURATION AS MAXBILLDURATION, A.BUYCHARGE AS BUYCHARGE, A.SELLCHARGE AS SELLCHARGE, A.BUYMARGIN AS BUYMARGIN, A.SELLMARGIN AS SELLMARGIN, A.BUYFEE AS BUYFEE, A.SELLFEE AS SELLFEE,A.HOSTID AS HOSTID, A.HOST AS HOST, A.PREFIX AS PREFIX, A.EXCHANGE AS EXCHANGE,A.RTE AS RTE, A.SVC AS SVC
FROM (
SELECT R1.GROUPID AS GROUPID,
R1.TYPE AS TYPE,
COUNT(R1.CALLID) TOTAL,
SUM(IF(R1.ENDTIME=0 AND R1.BEGINTIME!=0,1,0)) AS ACTIVE,
SUM(IF(R1.ENDTIME!=0 AND R1.BEGINTIME!=0 AND R1.DURATION>0,1,0)) AS COMPLETED,
SUM(IF(R1.ALERTTIME>0 AND R1.ENDTIME!=0 AND R1.BEGINTIME!=0 AND R1.DURATION>0,R1.ALERTTIME-R1.CONNECTTIME,0)) AS ALERTDELAY,
SUM(R1.DURATION) AS DURATION, SUM(R1.BILLDURATION) AS BILLDURATION,
MAX(R1.BILLDURATION) AS MAXBILLDURATION,SUM(IF(R1.TYPE='buy',R1.GROUPAMOUNT,0)) AS BUYCHARGE,
SUM(IF(R1.TYPE='sell',R1.GROUPAMOUNT,0)) AS SELLCHARGE, SUM(IF(R1.TYPE='buy',R1.GROUPMARGIN,0)) AS BUYMARGIN,
SUM(IF(R1.TYPE='sell',R1.GROUPMARGIN,0)) AS SELLMARGIN, SUM(IF(R1.TYPE='buy',R1.GROUPFEE,0)) AS BUYFEE,
SUM(IF(R1.TYPE='sell',R1.GROUPFEE,0)) AS SELLFEE, R1.HOSTID AS HOSTID, R1.HOST AS HOST, R1.PREFIX AS PREFIX,
R1.EXCHANGE AS EXCHANGE,
R1.RTE AS RTE,
R1.SVC AS SVC
FROM RECORDS R1 FORCE INDEX(PARTITIONIDTIME)
INNER JOIN CALLS C1 ON R1.CALLID = C1.CALLID
WHERE R1.PARTITIONID=1 AND R1.CONNECTTIME>= 1199682000 AND R1.CONNECTTIME<= 1199689200 AND R1.TYPE ='buy'
AND R1.GROUPID = 229 AND R1.STATE!='retracted'
GROUP BY R1.GROUPID, R1.TYPE)A
GROUP BY A.GROUPID, A.TYPE
UNION
SELECT B.GROUPID AS ID, B.TYPE AS TYPE, B.TOTAL AS TOTAL, B.ACTIVE AS ACTIVE, B.COMPLETED AS COMPLETED, B.ALERTDELAY AS ALERTDELAY, B.DURATION AS DURATION, B.BILLDURATION AS BILLDURATION, B.MAXBILLDURATION AS MAXBILLDURATION, B.BUYCHARGE AS BUYCHARGE, B.SELLCHARGE AS SELLCHARGE, B.BUYMARGIN AS BUYMARGIN, B.SELLMARGIN AS SELLMARGIN, B.BUYFEE AS BUYFEE, B.SELLFEE AS SELLFEE, B.HOSTID AS HOSTID, B.HOST AS HOST, B.PREFIX AS PREFIX, B.EXCHANGE AS EXCHANGE, B.RTE AS RTE, B.SVC AS SVC
FROM (
SELECT R11.GROUPID AS GROUPID,
R11.TYPE AS TYPE,
COUNT(R11.CALLID) AS TOTAL,
SUM(IF(R11.ENDTIME=0 AND R11.BEGINTIME!=0,1,0)) AS ACTIVE,
SUM(IF(R11.ENDTIME!=0 AND R11.BEGINTIME!=0 AND R11.DURATION>0,1,0)) AS COMPLETED,
SUM(IF(R11.ALERTTIME>0 AND R11.ENDTIME!=0 AND R11.BEGINTIME!=0 AND R11.DURATION>0, R11.ALERTTIME-R11.CONNECTTIME,0)) AS ALERTDELAY,
SUM(R11.DURATION) AS DURATION, SUM(R11.BILLDURATION) AS BILLDURATION, MAX(R11.BILLDURATION) AS MAXBILLDURATION,
SUM(IF(R11.TYPE='buy',R11.GROUPAMOUNT,0)) AS BUYCHARGE, SUM(IF(R11.TYPE='sell',R11.GROUPAMOUNT,0)) AS SELLCHARGE,
SUM(IF(R11.TYPE='buy',R11.GROUPMARGIN,0)) AS BUYMARGIN, SUM(IF(R11.TYPE='sell',R11.GROUPMARGIN,0)) AS SELLMARGIN,
SUM(IF(R11.TYPE='buy',R11.GROUPFEE,0)) AS BUYFEE, SUM(IF(R11.TYPE='sell',R11.GROUPFEE,0)) AS SELLFEE, R11.HOSTID AS HOSTID,
R11.HOST AS HOST,
R11.PREFIX AS PREFIX,
R11.EXCHANGE AS EXCHANGE,
R11.RTE AS RTE, R11.SVC AS SVC,
R11.PARTITIONID AS PARTITIONID
FROM(
SELECT R.CALLID AS CALLID,
R.GROUPID AS GROUPID,
R.TYPE AS TYPE,
R.ENDTIME AS ENDTIME,
R.BEGINTIME AS BEGINTIME,
R.DURATION,
R.ALERTTIME AS ALERTTIME,
R.BILLDURATION AS BILLDURATION,
R.CONNECTTIME AS CONNECTTIME,
R.GROUPAMOUNT AS GROUPAMOUNT,
R.GROUPMARGIN AS GROUPMARGIN,
R.GROUPFEE AS GROUPFEE,
R.HOSTID AS HOSTID,
R.HOST AS HOST,
R.PREFIX AS PREFIX,
R.EXCHANGE AS EXCHANGE,
R.RTE AS RTE,
R.SVC AS SVC,
R.PARTITIONID AS PARTITIONID
FROM RECORDS R FORCE INDEX(PARTITIONIDTIME)
WHERE R.PARTITIONID=1 AND R.CONNECTTIME>= 1199682000 AND R.CONNECTTIME<= 1199689200 AND R.TYPE= 'sell') AS R11
INNER JOIN(SELECT R11.CALLID AS CALLID
FROM RECORDS R11 FORCE INDEX(PARTITIONIDTIME)
INNER JOIN CALLS C1 ON R11.CALLID = C1.CALLID
WHERE R11.PARTITIONID=1 AND R11.CONNECTTIME>= 1199682000 AND R11.CONNECTTIME<= 1199689200 AND R11.TYPE ='buy'
AND R11.GROUPID = 229 )AS R22 ON R11.CALLID = R22.CALLID
GROUP BY R11.GROUPID, R11.TYPE )AS B
)AS C LEFT JOIN GROUPS AS G ON C.ID = G.GROUPID GROUP BY C.ID, C.TYPE ORDER BY C.TYPE;
***********************************************************