Problème performance sous requête MySQL

**Razielwar** · 22/02/2012, 14h55

Bonjour,

Je rencontre des soucis de performance avec des sous-requêtes en MySQL 5 et j'avoue ne pas bien comprendre pourquoi. J'ai plusieurs requêtes, qui exécutées séparément sont très rapides mais dont l'imbrication fait chuter les performances.

Les requêtes séparées:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
select distinct f.to_icao from flight f, airline a where (f.airline_id = a.id or f.suboperator_id = a.id) and a.name = 'Air Nostrum'
0.047sec
 
to_icao
LEMD
LEAL
LEVC
GCRR
LEPP
GCFV
LEGR
LEPA
EDDM
LEZL
LIPZ
LOWW
LGAV
LEMG
LEAS
LPPT
LSZH
LIRF
LSGG
EDDF
LIMC
LFPO
EDDL
LEVX
LEJR
LEST
LEIB
GCXO
GCLP
EIDW
LELC
GEML
LEAM
LEBZ
LEBL
LESO
LEBB
LECO
GMMX
GMTT
GMME
LEXJ
LELN
LEMH
EGPF
EGCC
LFLC
LEVD
LFRS
LFBD
LIRN
LEDA
LIPE
LFST
LESA
LFLL
LEBG
LPPR
LFMN
LFBO
LIMF
LIRP
LFMT
LIMJ
LFML

Requête suivante:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
select m.station, 
	   count(distinct m.id) as Nb_Metar,
        (select count(distinct m5.id) from metar m5, cloud c5, visibility v5 where m.station = m5.station and m5.id = c5.metar_id and m5.id = v5.metar_id and ((c5.amount = 'BKN' and c5.height < 600) or v5.distance < 3200)) as Cloud_Inf_600_OR_Vis_Inf_3200,
        (select count(distinct m6.id) from metar m6, cloud c6, visibility v6 where m.station = m6.station and m6.id = c6.metar_id and m6.id = v6.metar_id and ((c6.amount = 'BKN' and c6.height < 250) or v6.distance < 900)) as Cloud_Inf_250_OR_Vis_Inf_900
from metar m
where m.station in ('LEMD','LEAL','LEVC','GCRR','LEPP','GCFV','LEGR','LEPA','EDDM','LEZL','LIPZ','LOWW','LGAV',
'LEMG','LEAS','LPPT','LSZH','LIRF','LSGG','EDDF','LIMC','LFPO','EDDL','LEVX','LEJR','LEST','LEIB','GCXO','GCLP',
'EIDW','LELC','GEML','LEAM','LEBZ','LEBL','LESO','LEBB','LECO','GMMX','GMTT','GMME','LEXJ','LELN','LEMH','EGPF',
'EGCC','LFLC','LEVD','LFRS','LFBD','LIRN','LEDA','LIPE','LFST','LESA','LFLL','LEBG','LPPR','LFMN','LFBO','LIMF','LIRP','LFMT','LIMJ','LFML')
group by m.station
35.906sec

Sachant que j'ai des index sur metar.station, airline.id, flight.to_icao, flight.airline_id, flight.suboperator_id

En regroupant le tout cela donne ça:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select m.station, 
	   count(distinct m.id) as Nb_Metar,
        (select count(distinct m5.id) from metar m5, cloud c5, visibility v5 where m.station = m5.station and m5.id = c5.metar_id and m5.id = v5.metar_id and ((c5.amount = 'BKN' and c5.height < 600) or v5.distance < 3200)) as Cloud_Inf_600_OR_Vis_Inf_3200,
        (select count(distinct m6.id) from metar m6, cloud c6, visibility v6 where m.station = m6.station and m6.id = c6.metar_id and m6.id = v6.metar_id and ((c6.amount = 'BKN' and c6.height < 250) or v6.distance < 900)) as Cloud_Inf_250_OR_Vis_Inf_900
from metar m
where m.station in (select distinct f.to_icao from flight f, airline a where (f.airline_id = a.id or f.suboperator_id = a.id) and a.name = 'Air Nostrum')
group by m.station

Pas de résultats après plus de 2 minutes d'attente alors que je m'attendais à ce que le temps des deux requêtes précédentes s'additionne.

J'ai essayé aussi par jointure sans succès non plus:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
select m.station, 
	   count(distinct m.id) as Nb_Metar,
        (select count(distinct m5.id) from metar m5, cloud c5, visibility v5 where m.station = m5.station and m5.id = c5.metar_id and m5.id = v5.metar_id and ((c5.amount = 'BKN' and c5.height < 600) or v5.distance < 3200)) as Cloud_Inf_600_OR_Vis_Inf_3200,
        (select count(distinct m6.id) from metar m6, cloud c6, visibility v6 where m.station = m6.station and m6.id = c6.metar_id and m6.id = v6.metar_id and ((c6.amount = 'BKN' and c6.height < 250) or v6.distance < 900)) as Cloud_Inf_250_OR_Vis_Inf_900
from metar m, flight f, airline a
where m.station = f.to_icao and (f.airline_id = a.id or f.suboperator_id = a.id) and a.name = 'Air Nostrum'
group by m.station

Est ce que quelqu'un a déjà rencontré ce cas de figure? Je souhaiterais n'avoir à faire qu'une seule requête.

En vous remerciant

**CinePhil** · 22/02/2012, 15h06

Les requêtes imbriquées dans le SELECT, c'est sûr que c'est pas top !

Les jointures vieilles de 20 ans non plus d'ailleurs !

Tu peux expliquer ce qu'est censée faire la requête ?

EDIT :
Si j'interprète correctement la partie des SELECTs imbriqués, tu cherches à compter le nombre de metar par station selon deux conditions différentes.

Je pense alors que cette requête répond au besoin :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT m.station, 
	SUM
	(
		CASE
			WHEN (c.amount = 'BKN' AND c.height < 600) 
					OR v.distance < 3200
				THEN 1
			ELSE 0
		END
	) AS Cloud_Inf_600_OR_Vis_Inf_3200,
	SUM
	(
		CASE
			WHEN (c.amount = 'BKN' AND c.height < 250) 
					OR v.distance < 900
				THEN 1
			ELSE 0
		END
	) AS Cloud_Inf_250_OR_Vis_Inf_900
FROM metar m
INNER JOIN cloud c ON m.id = c.metar_id
INNER JOIN visibility v ON m.id = v.metar_id
INNER JOIN flight f ON m.station = f.to_icao
	INNER JOIN airline a ON f.airline_id = a.id OR f.suboperator_id = a.id
WHERE a.name = 'Air Nostrum'
GROUP BY m.station

**Razielwar** · 22/02/2012, 15h16

En gros, il s'agit d'analyser des données météo sur des aéroports et de comptabiliser le nombre de relevés qui répondent à certains critères de visibilité et de hauteur de couches nuageuse.

La jointure ou sous sélection permet de n'effectuer ce calcul statistique que sur les aéroports de destination d'une compagnie aérienne donnée. Ici, Air Nostrum.

Je ne peux me permettre de faire l'analyse sur toutes les stations météo car le volume de données est conséquent. La requête qui prend trente secondes travaille déjà sur 4 millions de lignes.

Voici un exemple de résultats:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Station NbMetar Cloud_Inf_600_OR_Vis_Inf_3200 Cloud_Inf_250_OR_Vis_Inf_900
EDDF	34826	1432	                                           646
EDDL	34824	1416	                                           242
EDDM	34826	4304	                                           1706
EGCC	34350	1408	                                           348
EGPF	17369	812	                                           286
EIDW	17377	422	                                           122
GCFV	12722	11	                                           0
GCLP	17376	54	                                           1
...     ...	...   	                                           ...

**CinePhil** · 22/02/2012, 15h21

J'ai édité ma réponse avec une proposition de requête.

**Razielwar** · 22/02/2012, 15h52

Pas mieux...

J'ai fait tourner ta requête pendant plus de 3 minutes sans résultat. De plus, il me manque le décompte total dans la requête que tu as écrite car au final, le but est d'obtenir un ratio par rapport au nombre total de relevés par station.

**Razielwar** · 22/02/2012, 16h24

C'est bien la sous requête select qui pause problème. Je me suis inspiré de ta méthode avec la fonction sum et case pour tester le résultat.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
select m.station, 
	   count(distinct m.id) as Nb_Metar,
	   SUM(CASE WHEN (c.amount = 'BKN' AND c.height < 600) OR v.distance < 3200 THEN 1 ELSE 0 END) AS Cloud_Inf_600_OR_Vis_Inf_3200,
	   SUM(CASE WHEN (c.amount = 'BKN' AND c.height < 250) OR v.distance < 900 THEN 1 ELSE 0 END) AS Cloud_Inf_250_OR_Vis_Inf_900,
        (select count(distinct m5.id) from metar m5, cloud c5, visibility v5 where m.station = m5.station and m5.id = c5.metar_id and m5.id = v5.metar_id and ((c5.amount = 'BKN' and c5.height < 600) or v5.distance < 3200)) as Cloud_Inf_600_OR_Vis_Inf_3200,
        (select count(distinct m6.id) from metar m6, cloud c6, visibility v6 where m.station = m6.station and m6.id = c6.metar_id and m6.id = v6.metar_id and ((c6.amount = 'BKN' and c6.height < 250) or v6.distance < 900)) as Cloud_Inf_250_OR_Vis_Inf_900
from metar m
LEFT OUTER JOIN cloud c on c.metar_id = m.id
LEFT OUTER JOIN visibility v on v.metar_id = m.id
where m.station = 'LEMD'
group by m.station

Et j'obtiens ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

LEMD	17549	564	175	353	122

Le problème c'est que j'ai plusieurs visibility et cloud par metar donc j'avais fait un select distinct pour ne pas comptabiliser un metar plusieurs fois. Du coup, l'utilisation du case ne me convient pas.

Donc pour le moment, le mystère de la sous requête qui prend un temps infini reste entier.

Si quelqu'un a une idée...

**ericd69** · 22/02/2012, 18h08

salut,

as tu regardé avec un explain si tu avais pas un problème de régénération de ta sous-requêtes à chaque itération de ta requête principale?

j'ai déjà vu ce bug qu'un type avait décrit ici sur le forum...

la table temporaire (générée automatiquement en interne) correspondant à la sous-requête était refaite à chaque fois ce qui rendait le temps d'exécution prohibitif... visiblement un bug de mysql sur certains configurations de sous-requêtes...

une solution, qui peut améliorer peut-être un peu si l'écriture en une seul requête ne va pas est d'utiliser une procédure stockée qui va:

générer une table temporaire grace à ta sous-requête dans le in
exécuter ta requête principale en utilisant un simple select sur la table temporaire

après si tu as accès au paramétrages du serveur, tu peux aussi tenter d'augmenter un peu les valeurs de buffer pour les tables (et/ou celles pour les jointures)...

Problème performance sous requête MySQL

Requêtes MySQL

Vue hybride

Discussions similaires

Partager

Partager