OPTIMIZE STORED PROCEDURE

MySQL & MariaDB Other MySQL® Questions
1

CAN SOMEONE HELP ME TO USE INDEX TO OPTIMIZE PREF IN THIS STORED PROC. BEST REGARDS

CREATE PROCEDURE statvenaai.METXGM( ) LANGUAGE SQL BEGIN CREATE TEMPORARY TABLE METXGM ( CAMPO1 CHAR(255),CAMPO2 CHAR(255),CAMPO3 CHAR(255),CAMPO4 CHAR(255), SALLYTD DECIMAL(10, 2),QTYLYTD DECIMAL(10, 2),SALLY DECIMAL(10, 2),QTYLY DECIMAL(10, 2),SALTYTD DECIMAL(10, 2),QTYTYTD DECIMAL(10, 2),SALTY DECIMAL(10, 2),QTYTY DECIMAL(10, 2),PORTD DECIMAL(10, 2),POR DECIMAL(10, 2),PORQTYTD DECIMAL(10, 2),PORQTY DECIMAL(10, 2)) ; DELETE FROM METXGM;INSERT INTO METXGM SELECT X.Agent_Code AS CAMPO1, X.Article_Code AS CAMPO2, X.CUSTOMER AS CAMPO3, X.MARKET AS CAMPO4 , SUM(X.Sales01 + X.Sales02 + X.Sales03 ) AS SALLYTD, SUM(X.Qty01 + X.Qty02 + X.Qty03 ) AS QTYLYTD, SUM(X.Sales01 + X.Sales02 + X.Sales03 + X.Sales04 + X.Sales05 + X.Sales06 + X.Sales07 + X.Sales08 + X.Sales09 + X.Sales10 + X.Sales11 + X.Sales12 ) AS SALLY, SUM(X.Qty01 + X.Qty02 + X.Qty03 + X.Qty04 + X.Qty05 + X.Qty06 + X.Qty07 + X.Qty08 + X.Qty09 + X.Qty10 + X.Qty11 + X.Qty12 ) AS QTYLY, 0 AS SALTYTD, 0 AS QTYTYTD, 0 AS SALTY, 0 AS QTYTY, 0 AS PORTD, 0 AS POR, 0 AS PORQTYTD, 0 AS PORQTY FROM statvenaai.sales_mesi AS X WHERE (((X.Type) = ‘F’ Or (X.Type) = ‘B’) AND ((X.Year) = ‘2008’) AND ((X.Agent_Code) = ’ PU’)) GROUP BY X.Agent_Code , X.Article_Code , X.CUSTOMER , X.MARKET HAVING ( X.Agent_Code IN (’ PU’) AND ( X.CUSTOMER LIKE ‘%’ ) AND (X.MARKET LIKE ‘%’) ) ;INSERT INTO METXGM SELECT X.Agent_Code AS CAMPO1, X.Article_Code AS CAMPO2, X.CUSTOMER AS CAMPO3, X.MARKET AS CAMPO4 , 0 AS SALLYTD, 0 AS QTYLYTD, 0 AS SALLY, 0 AS QTYLY, SUM(X.Sales01 + X.Sales02 + X.Sales03 ) AS SALTYTD, SUM(X.Qty01 + X.Qty02 + X.Qty03 ) AS QTYTYTD, SUM(X.Sales01 + X.Sales02 + X.Sales03 + X.Sales04 + X.Sales05 + X.Sales06 + X.Sales07 + X.Sales08 + X.Sales09 + X.Sales10 + X.Sales11 + X.Sales12 ) AS SALTY, SUM(X.Qty01 + X.Qty02 + X.Qty03 + X.Qty04 + X.Qty05 + X.Qty06 + X.Qty07 + X.Qty08 + X.Qty09 + X.Qty10 + X.Qty11 + X.Qty12 ) AS QTYTY, 0 AS PORTD, 0 AS POR, 0 AS PORQTYTD, 0 AS PORQTY FROM statvenaai.sales_mesi AS X WHERE (((X.Type) = ‘F’ Or (X.Type) = ‘B’) AND ((X.Year) = ‘2009’) AND ((X.Agent_Code) = ’ PU’)) GROUP BY X.Agent_Code , X.Article_Code , X.CUSTOMER , X.MARKET HAVING ( X.Agent_Code IN (’ PU’) AND ( X.CUSTOMER LIKE ‘%’ ) AND (X.MARKET LIKE ‘%’) );INSERT INTO METXGM SELECT X.Agent_Code AS CAMPO1, X.Article_Code AS CAMPO2, X.CUSTOMER AS CAMPO3, X.MARKET AS CAMPO4 , 0 AS SALLYTD, 0 AS QTYLYTD, 0 AS SALLY, 0 AS QTYLY, 0 AS SALTYTD, 0 AS QTYTYTD, 0 AS SALTY, 0 AS QTYTY, SUM(X.Sales01 + X.Sales02 + X.Sales03 ) AS PORTD, SUM(X.Sales01 + X.Sales02 + X.Sales03 + X.Sales04 + X.Sales05 + X.Sales06 + X.Sales07 + X.Sales08 + X.Sales09 + X.Sales10 + X.Sales11 + X.Sales12 ) AS POR, SUM(X.Qty01 + X.Qty02 + X.Qty03 ) AS PORQTYTD, SUM(X.Qty01 + X.Qty02 + X.Qty03 ) AS PORQTY FROM statvenaai.backlog_mesi AS X WHERE (((X.Type) = ‘R’) AND ((X.Agent_Code) = ’ PU’)) GROUP BY X.Agent_Code , X.Article_Code , X.CUSTOMER , X.MARKET HAVING ( X.Agent_Code IN (’ PU’) AND ( X.CUSTOMER LIKE ‘%’ ) AND (X.MARKET LIKE ‘%’) );SELECT X.CAMPO1, X.CAMPO2, X.CAMPO3, X.CAMPO4, SUM(X.SALTYTD) AS SALTYTD, SUM(X.PORTD) AS PORTD, SUM(X.SALLY) AS SALLY, SUM(X.SALLYTD) AS SALLYTD, SUM(X.QTYLYTD) AS QTYLYTD, SUM(X.QTYLY) AS QTYLY, SUM(X.SALTY) AS SALTY, SUM(X.QTYTYTD) AS QTYTYTD, SUM(X.QTYTY) AS QTYTY, SUM(X.PORQTYTD) AS PORQTYTD, SUM(X.POR) AS POR, SUM(X.PORQTY) AS PORQTY FROM METXGM AS X GROUP BY X.CAMPO1, X.CAMPO2, X.CAMPO3, X.CAMPO4 ORDER BY SALTY DESC ; END

2

First, I highly recommend trying to format your SQL for readability. It’s hard to look at.

Your HAVING clauses seem unnecessary, since they are either covered by your WHERE clause or contain LIKE ‘%’. I’m not sure this would effect performance, but it is certainly strange.

Also, why are you deleting from a temporary table right after you create it? Using DROP TABLE IF EXISTS makes more sense if you’re worried about a pre-existing temp table.

To get at performance concerns you should post the output of the EXPLAIN command for any of the selects you do against non-temporary tables. Also posting the output of SHOW INDEXES FROM and SHOW TABLE STATUS for the tables used in these queries would be helpful.