Igor
Reputation: 2659
MySQL query optimization hint needed
could you please help me with a monster.
Do you see any issue with this one?
Would like to reach the execution time below the second, is it possible?
Please ask for any other data you may need to understand the structure of DB. Any tips&tricks are welcome!
SELECT
ORD_CLI.COD_AGE,
ORD_CLI_RIGHE.DOC_ID,
OFF_CLI.off_cli_id,
ORD_CLI_RIGHE.DOC_RIGA_ID,
ORD_CLI_RIGHE.COD_ART,
ART_PESO.PESO_ART,
ORD_CLI.ANNO_DOC,
ORD_CLI.NUM_DOC,
ORD_CLI.SERIE_DOC,
ORD_CLI.DATA_DOC,
CF.RAG_SOC_CF,
AGENTI.NOME_AGE,
ORD_CLI.COD_CF,
ORD_CLI.COD_IVA,
ORD_CLI.COD_DEP,
ORD_CLI_TOT.IMPONIBILE_V1 AS IMPONIBILE_ORDINE,
FATT_CLI_TOT.IMPONIBILE_V1 AS IMPONIBILE_FATTURA,
ORD_CLI_TOT.IVA_V1,
SUM(ART_PESO.PESO_ART) AS weight,
SUM(FATT_CLI_RIGHE.QUANT_RIGA) AS quantity,
SUM(FATT_CLI_RIGHE.QUANT_RIGA*FATT_CLI_RIGHE.PREZZO_LORDO_VU1) AS sell_price,
SUM(FATT_CLI_RIGHE.QUANT_RIGA*DDT_FOR_RIGHE.PREZZO_LORDO_VU1) AS acqisition_price1,
SUM(FATT_CLI_RIGHE.QUANT_RIGA*FATT_FOR_RIGHE.PREZZO_LORDO_VU1) AS acqisition_price2,
SUM(FATT_CLI_RIGHE.QUANT_RIGA*FATT_CLI_RIGHE_PROVV.IMPORTO_PROVV_VU1) AS agent_reward,
SUM(FATT_CLI_RIGHE.QUANT_RIGA*ART_PESO.PESO_ART * 0.13) AS transport_price,
SUM(FATT_CLI_RIGHE.QUANT_RIGA*(
FATT_CLI_RIGHE.PREZZO_LORDO_VU1
- COALESCE(DDT_FOR_RIGHE.PREZZO_LORDO_VU1, 0)
- COALESCE(FATT_FOR_RIGHE.PREZZO_LORDO_VU1, 0)
- COALESCE(FATT_CLI_RIGHE_PROVV.IMPORTO_PROVV_VU1, 0)
- COALESCE(ART_PESO.PESO_ART, 0) * 0.13
)) AS net_earning,
OFF_CLI.stima_prezzo_acquisto,
OFF_CLI.stima_prezzo_trasporto,
OFF_CLI.stima_provvigioni_agenti,
OFF_CLI.stima_utile
FROM ORD_CLI
INNER JOIN ORD_CLI_RIGHE
ON ORD_CLI_RIGHE.DOC_ID = ORD_CLI.DOC_ID
LEFT JOIN ORD_CLI_RIGHE_SPEC
ON ORD_CLI_RIGHE.DOC_RIGA_ID = ORD_CLI_RIGHE_SPEC.DOC_RIGA_ID
INNER JOIN ART_PESO
ON ART_PESO.COD_ART = ORD_CLI_RIGHE.COD_ART
INNER JOIN ORD_CLI_TOT
ON ORD_CLI.DOC_ID = ORD_CLI_TOT.DOC_ID
INNER JOIN AGENTI
ON AGENTI.COD_AGE = ORD_CLI.COD_AGE
INNER JOIN CF
ON CF.COD_CF = ORD_CLI.COD_CF
LEFT JOIN FATT_CLI_RIGHE_SPEC
ON ORD_CLI_RIGHE.DOC_RIGA_ID = FATT_CLI_RIGHE_SPEC.ORD_RIGA_ID
LEFT JOIN FATT_CLI_RIGHE
ON FATT_CLI_RIGHE.DOC_RIGA_ID = FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID
LEFT JOIN FATT_CLI_TOT
ON FATT_CLI_RIGHE.DOC_ID = FATT_CLI_TOT.DOC_ID
LEFT JOIN FATT_CLI_RIGHE_PROVV
ON FATT_CLI_RIGHE_PROVV.DOC_RIGA_ID = FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID
LEFT JOIN FATT_CLI_RIGHE_LOTTI
ON FATT_CLI_RIGHE_LOTTI.DOC_RIGA_ID = FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID
LEFT JOIN DDT_FOR_RIGHE_LOTTI
ON DDT_FOR_RIGHE_LOTTI.COD_LOT = FATT_CLI_RIGHE_LOTTI.COD_LOT
LEFT JOIN DDT_FOR_RIGHE
ON DDT_FOR_RIGHE.DOC_RIGA_ID = DDT_FOR_RIGHE_LOTTI.DOC_RIGA_ID
LEFT JOIN FATT_FOR_RIGHE
ON FATT_FOR_RIGHE.DOC_RIGA_ID = FATT_CLI_RIGHE_LOTTI.COD_LOT
LEFT JOIN OFF_CLI_RIGHE
ON OFF_CLI_RIGHE.DOC_RIGA_ID = ORD_CLI_RIGHE_SPEC.OFF_RIGA_ID
LEFT JOIN OFF_CLI
ON OFF_CLI.DOC_ID = OFF_CLI_RIGHE.DOC_ID
WHERE
ORD_CLI.COD_BUSN_UN='P'
AND OFF_CLI_RIGHE.DOC_RIGA_ID IS NOT NULL
AND ORD_CLI.DATA_DOC >= '2012-11-29'
AND ORD_CLI.DATA_DOC <= '2013-02-28'
GROUP BY ORD_CLI.DOC_ID
ORDER BY ORD_CLI.DATA_DOC
DESC LIMIT 30 OFFSET 0
Time of execution
Showing rows 0 - 29 ( 30 total, Query took 6.3458 sec)
EXPLAIN of the query
+----+-------------+----------------------+--------+-----------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------------+--------+-----------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | ORD_CLI | range | PRIMARY,ORD_CLI_DATA_DOC,ORD_CLI_COD_CF,ORD_CLI_COD_BUSN_UN,ORD_CLI_COD_AGE | ORD_CLI_DATA_DOC | 4 | NULL | 3728 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | AGENTI | eq_ref | PRIMARY | PRIMARY | 38 | ORD_CLI.COD_AGE | 1 | 100.00 | Using where |
| 1 | SIMPLE | CF | eq_ref | PRIMARY | PRIMARY | 38 | ORD_CLI.COD_CF | 1 | 100.00 | |
| 1 | SIMPLE | ORD_CLI_TOT | eq_ref | PRIMARY | PRIMARY | 62 | ORD_CLI.DOC_ID | 1 | 100.00 | |
| 1 | SIMPLE | ORD_CLI_RIGHE | ref | PRIMARY,ORD_CLI_RIGHE_DOC_ID,ORD_CLI_RIGHE_COD_ART | ORD_CLI_RIGHE_DOC_ID | 62 | ORD_CLI_TOT.DOC_ID | 2 | 100.00 | Using where |
| 1 | SIMPLE | ART_PESO | eq_ref | PRIMARY | PRIMARY | 92 | ORD_CLI_RIGHE.COD_ART | 1 | 100.00 | |
| 1 | SIMPLE | ORD_CLI_RIGHE_SPEC | eq_ref | PRIMARY,ORD_CLI_RIGHE_SPEC_OFF_RIGA_ID | PRIMARY | 92 | ORD_CLI_RIGHE.DOC_RIGA_ID | 1 | 100.00 | Using where |
| 1 | SIMPLE | OFF_CLI_RIGHE | ref | DOC_RIGA_ID | DOC_RIGA_ID | 92 | ORD_CLI_RIGHE_SPEC.OFF_RIGA_ID | 1 | 100.00 | Using where |
| 1 | SIMPLE | OFF_CLI | ref | DOC_ID | DOC_ID | 63 | OFF_CLI_RIGHE.DOC_ID | 1 | 100.00 | |
| 1 | SIMPLE | FATT_CLI_RIGHE_SPEC | ref | FATT_CLI_RIGHE_SPEC_ORD_RIGA_ID | FATT_CLI_RIGHE_SPEC_ORD_RIGA_ID | 93 | ORD_CLI_RIGHE.DOC_RIGA_ID | 1 | 100.00 | Using index |
| 1 | SIMPLE | FATT_CLI_RIGHE | eq_ref | PRIMARY | PRIMARY | 92 | FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID | 1 | 100.00 | |
| 1 | SIMPLE | FATT_CLI_TOT | eq_ref | PRIMARY | PRIMARY | 62 | FATT_CLI_RIGHE.DOC_ID | 1 | 100.00 | |
| 1 | SIMPLE | FATT_CLI_RIGHE_PROVV | ref | FATT_CLI_RIGHE_PROVV_DOC_RIGA_ID | FATT_CLI_RIGHE_PROVV_DOC_RIGA_ID | 92 | FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID | 1 | 100.00 | |
| 1 | SIMPLE | FATT_CLI_RIGHE_LOTTI | ref | FATT_CLI_RIGHE_LOTTI_DOC_RIGA_ID | FATT_CLI_RIGHE_LOTTI_DOC_RIGA_ID | 92 | FATT_CLI_RIGHE_SPEC.DOC_RIGA_ID | 1 | 100.00 | |
| 1 | SIMPLE | DDT_FOR_RIGHE_LOTTI | ref | DDT_FOR_RIGHE_LOTTI_COD_LOT | DDT_FOR_RIGHE_LOTTI_COD_LOT | 92 | FATT_CLI_RIGHE_LOTTI.COD_LOT | 1 | 100.00 | |
| 1 | SIMPLE | DDT_FOR_RIGHE | eq_ref | PRIMARY | PRIMARY | 92 | DDT_FOR_RIGHE_LOTTI.DOC_RIGA_ID | 1 | 100.00 | |
| 1 | SIMPLE | FATT_FOR_RIGHE | eq_ref | PRIMARY | PRIMARY | 92 | FATT_CLI_RIGHE_LOTTI.COD_LOT | 1 | 100.00 | |
+----+-------------+----------------------+--------+-----------------------------------------------------------------------------+----------------------------------+---------+--------------------------------------------+------+----------+----------------------------------------------+
The following is the result of show status like 'Handler%' excatly after the query been executed
Handler_commit, 2
Handler_delete, 0
Handler_discover, 0
Handler_prepare, 0
Handler_read_first, 0
Handler_read_key, 421001
Handler_read_last, 0
Handler_read_next, 240344
Handler_read_prev, 0
Handler_read_rnd, 30
Handler_read_rnd_next, 2412
Handler_rollback, 0
Handler_savepoint, 0
Handler_savepoint_rollback, 0
Handler_update, 31846
Handler_write, 2409
Database structure: https://gist.github.com/moiseevigor/4988fc8868f92643c9fb
EDIT 1
After creation of index
ALTER TABLE `TCross5_NP`.`ORD_CLI`
ADD INDEX `ORD_CLI_MULTI` (`COD_BUSN_UN` ASC, `DATA_DOC` ASC, `DOC_ID` ASC) ;
The execution time gone down 2 times, but still hits the ORD_CLI_MULTI index
Upvotes: 1
Views: 245
Answers (1)
DRapp
Reputation: 48139
First, (and has helped in many other similar queries where you appear to be dealing with a lot of "lookup" secondary table references), change start of query to
SELECT STRAIGHT_JOIN
Which directs the engine to run the query in the exact order you have listed. This will prevent it from trying to use a lookup table as a primary consideration and trying to work backwords or end-around to get the data. Sometimes works well, other times (rarely in my experience), hinders performance.
Next, since you are looking for an " AND OFF_CLI_RIGHE.DOC_RIGA_ID IS NOT NULL", I would change your LEFT JOINs to INNER JOIN when joining to.
INNER JOIN ORD_CLI_RIGHE_SPEC
ON ORD_CLI_RIGHE.DOC_RIGA_ID = ORD_CLI_RIGHE_SPEC.DOC_RIGA_ID
INNER JOIN OFF_CLI_RIGHE
ON ORD_CLI_RIGHE_SPEC.OFF_RIGA_ID = OFF_CLI_RIGHE.DOC_RIGA_ID
and thus eliminate the "AND ... is not null" in the WHERE clause.
Finally, I would have an index that is multiple parts that can be optimized FOR the query...
CREATE index MultipleParts on ORD_CLI ( COD_BUSN_UN, DATA_DOC, DOC_ID );
The multipart index will help the WHERE, GROUP BY AND ORDER BY of the query.
Upvotes: 1