我准备了以下mysql select查询来检索一些数据。
这是选择查询,
SELECT MA.agentCode,MA.agentName,MP.code,MP.name, SUM(LG.ITEM_QTY) AS 'TOTAL',LG.FK_PUBLICATION
,SUM(RAI.REURN_COUNT) AS 'TOTAL_RETURNS'
FROM LABEL_GENERATED_ORDER_ITEM LG
INNER JOIN MSTR_PUBLICATION MP ON MP.id=LG.FK_PUBLICATION
INNER JOIN RETURNS_ACCEPTANCE_FLOW RA ON RA.FK_AGENT=LG.FK_AGENT
INNER JOIN MSTR_AGENT MA ON MA.id=LG.FK_AGENT
INNER JOIN RETURNS_ACCEPTANCE_FLOW_ITEM RAI ON RAI.FK_RETURN_ACCEPTANCE_FLOW=RA.ID
WHERE MA.SALES_INCENTIVE_ALLOWED=1 AND MA.FK_AGENT_TYPE=1 AND
LG.CIRCULATION_DATE BETWEEN '2017-05-01' and '2017-05-29'
AND RAI.SCANNED_DATE BETWEEN '2017-05-01' and '2017-05-29'
AND RAI.IS_NORMAL_APPROVED=1 AND
RAI.IS_SPECIAL_APPROVED=1
GROUP BY MA.agentCode, MP.code ,MA.agentName,MP.name,LG.FK_PUBLICATION
ORDER BY MA.agentCode ASC, MP.code ASC;
以上查询工作正常。但执行起来要花很多时间。我想减少/优化查询的执行时间。
选择查询的结果作为文本,
agentCode agentName code name TOTAL FK_PUBLICATION TOTAL_RETURNS
1036 M.A. CHANDRANI SILVA, ARG AROGYA 744 20 860
1036 M.A. CHANDRANI SILVA, BUD BUDUSARANA 465 7 860
1036 M.A. CHANDRANI SILVA, CDN DAILY NEWS 104067 1 5375
1036 M.A. CHANDRANI SILVA, COM SUNDAY OBSERVER 11160 4 860
1036 M.A. CHANDRANI SILVA, DIN DINAMINA 25203 2 5375
1036 M.A. CHANDRANI SILVA, MCH MANCHU 2976 9 860
1036 M.A. CHANDRANI SILVA, MIH MIHIRA 465 10 1075
1036 M.A. CHANDRANI SILVA, SAV SARASAVIYA 1860 12 860
1036 M.A. CHANDRANI SILVA, SIL SILUMINA 4092 5 860
1036 M.A. CHANDRANI SILVA, SIM SITHMINA 930 18 1075
1036 M.A. CHANDRANI SILVA, SUB SUBASETHA 1116 13 860
1036 M.A. CHANDRANI SILVA, THA THARUNEE 2232 14 860
1036 M.A. CHANDRANI SILVA, TKN DAILY THINAKARAN 2325 3 5375
你有什么想法吗?
暂无答案!
目前还没有任何答案,快来回答吧!