如何减少mysql查询执行时间

bwleehnv  于 2021-06-19  发布在  Mysql
关注(0)|答案(0)|浏览(271)

我准备了以下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

你有什么想法吗?

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题