MySQL Forums
Forum List  »  Performance

Slow query on tmp table
Posted by: Eric Colson
Date: November 19, 2015 09:02AM

Hi all,

I Try to execute this query:

SELECT str_nom_magasin, e.Str_Code_Devise,
COUNT(ID_Ticket) AS nb_tickets,
SUM(Dbl_Montant_TTC_Rem) / (SELECT dbl_taux_conv_dev_pale from t_devise WHERE code_devise = e.Str_Code_Devise) AS ca_ttc,
SUM(DBL_Montant_Marge) / (SELECT dbl_taux_conv_dev_pale from t_devise WHERE code_devise = e.Str_Code_Devise) AS marge
FROM t_ent_ticket e
INNER JOIN t_magasin m ON m.id_magasin = e.id_magasin
WHERE str_dat_valid BETWEEN "20151109" AND "20151116"
AND e.int_est_annule = 0 AND e.int_est_attente = 0
GROUP BY 1, 2
ORDER BY 4 DESC;

Sometimes a few seconds are required, sometimes a lot of minuts.

Here's the EXPLAIN:
"id" "select_type" "table" "type" "possible_keys" "key" "key_len" "ref" "rows" "Extra"
"1" "PRIMARY" "m" "ALL" "PRIMARY" \N \N \N "16" "Using temporary; Using filesort"
"1" "PRIMARY" "e" "ref" "DATES,HEURES,id_magasin" "id_magasin" "4" "caisse.m.ID_Magasin" "3708" "Using where"
"3" "DEPENDENT SUBQUERY" "t_devise" "ref" "DEV1" "DEV1" "33" "func" "1" "Using where"
"2" "DEPENDENT SUBQUERY" "t_devise" "ref" "DEV1" "DEV1" "33" "func" "1" "Using where"

Thanks in advance if you can help me.

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow query on tmp table
1186
November 19, 2015 09:02AM
605
November 19, 2015 12:08PM
747
November 26, 2015 02:44PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.