MySQL Forums :: Newbie :: how to solve this complex query


Advanced Search

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;
***********************************************************

Options: ReplyQuote


Subject Written By Posted
how to solve this complex query aftab ali 01/18/2009 11:28PM
Re: how to solve this complex query Rick James 01/19/2009 12:28AM
Re: how to solve this complex query aftab ali 01/19/2009 01:51AM
Re: how to solve this complex query aftab ali 01/19/2009 03:57AM
Re: how to solve this complex query Rick James 01/19/2009 10:10AM
Re: how to solve this complex query aftab ali 01/19/2009 11:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.