0

I prepared following mysql select query to retrieve some data.

Here is the select query,

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;

Above query is working perfectly. But it takes much time to execute. I want to reduce/optimize execution time of the query.

Result of the select query as text,

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

Have any ideas ?

Terance Wijesuriya
  • 1,928
  • 9
  • 31
  • 61
  • 3
    Post the result of `EXPLAIN SELECT ....`. – Amadan Oct 19 '18 at 11:36
  • pos off-topic here, better for code review SE – treyBake Oct 19 '18 at 11:40
  • @Amadan : I updated the post – Terance Wijesuriya Oct 19 '18 at 11:42
  • @ThisGuyHasTwoThumbs : Post has been updated. – Terance Wijesuriya Oct 19 '18 at 11:42
  • That is not the output of `EXPLAIN SELECT * FROM ... your entire monster here ...;` Also, note that we vastly prefer text over screenshots. – Amadan Oct 19 '18 at 11:43
  • @Amadan : How could you tell that ? – Terance Wijesuriya Oct 19 '18 at 11:52
  • 1
    `explain` tells you (and us) details about the execution plan (how MySQL processes the query), not the result (which we cannot use to answer your question unless you e.g. need a different result), and you should make sure it does not reveal private information. Just write `explain ` directly infront of your (working) query and post the result, see e.g. [here](https://stackoverflow.com/a/9183633) I assume you will also need to provide the table descriptions (`show create table tablename`) of most of your tables, at least `ma`, `mp` and `rai`. – Solarflare Oct 19 '18 at 12:33

0 Answers0