Requêtes très lentes faisant planter le serveur

**bugbug** · 06/01/2014, 19h31

Bonjour

Je m'arrache les cheveux avec une requête mysql qui apparait en boucle dans mon fichier de requêtes longues à exécuter.
Celle-ci me fait planter régulièrement le serveur et me monte le processus mysql à plus de 800% du CPU.
Je ne vois pas ce que je peux optimiser, pouvez-vous m'aider ?

Exemple de requête

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
SELECT DISTINCT NRef_site.id,
NRef_site.nom_fr,
NRef_site.x,
NRef_site.y,
NRef_site.position_approx,
NRef_site.img,
NRef_site.root,
NRef_site.langue,
NRef_site.star,
NRef_site.ville_sql,
NRef_site.type,
NRef_site.classe,
NRef_site.img,
NRef_site.cat,
NRef_site.cp,
NRef_site.chien,
NRef_site.wifi,
NRef_site.piscine,
NRef_site.prix_repas1,
NRef_site.type_annonce,
NRef_site.handicape,
NRef_site.selec2,
NRef_site.acroche_fr,
NRef_site.nbs_chambre,
NRef_site.nbs_place_sql,
(6366*acos(cos(radians(46.683331))*cos(radians(NRef_site.x))*cos(radians(NRef_site.y)-radians(-1.766670))+sin(radians(46.683331))*sin(radians(NRef_site.x)))) as Proximite
from NRef_site 
left join prestation_annonce on prestation_annonce.annonce=NRef_site.id
WHERE prestation_annonce.prestation in (150)
and (prestation_annonce.date_fin='0000-00-00 00:00:00' or prestation_annonce.date_fin>='2014-01-06 14:48:37')
and NRef_site.actif=1
and NRef_site.selec=1 and NRef_site.type_annonce='location' and NRef_site.x!='' and NRef_site.selec2=3
group by NRef_site.id
HAVING COUNT(DISTINCT prestation_annonce.prestation) = 1 and Proximite>='0' and Proximite<='100' order by NRef_site.star='8' desc,NRef_site.star='6' desc,Proximite asc
limit 80;

La requête sert à sélectionner les annonces de la table NRef_site qui sont proche d'une coordonnées GPS et ayant un enregistrement ok dans la table prestation (prestation 150)

**m4riachi** · 08/01/2014, 16h01

Est-ce que les colonnes sont indexées comme (prestation_annonce.annonce, prestation_annonce.prestatio, prestation_annonce.date_fin, NRef_site.actif, NRef_site.selec, NRef_site.type_annonce, NRef_site.x, NRef_site.selec2)

**ced** · 09/01/2014, 15h34

Bonjour,

Avant de se plonger dans les index, il faut surtout reprendre la requête... Parce que si je comprends (je pense) ce que vous cherchez à faire, je suis quasi certain que cette requête ne renvoie pas quelque chose de conforme à vos attentes...

C'est fou comme MySQL est permissif sur la syntaxe des requêtes. Parce que celle-là, sur un autre SGBD, elle ne s'exécuterait pas...

Bref.

Voici quelques-uns des problèmes qu'on peut voir et qui, sans nul doute, jouent sur les performances :

Le DISTINCT et le GROUP BY ensemble, le tout sans fonction d'agrégation dans la requête, ça ne fonctionne pas... ;
La clause HAVING avec Proximité dedans ;
Des "=" dans le GROUP BY (je me demande bien ce que ça peut bien faire, ça...) ;
Les quotes autour des valeurs min et max de la proximité, qui force un transtypage et qui en plus compare des chaines de caractères, ce qui renverra un résultat complètement erroné ;
< et > au lieu de BETWEEN ;
etc.

Essayez plutôt quelque chose comme ça :

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
SELECT NRef_site.id,
NRef_site.nom_fr,
NRef_site.x,
NRef_site.y,
NRef_site.position_approx,
NRef_site.img,
NRef_site.root,
NRef_site.langue,
NRef_site.star,
NRef_site.ville_sql,
NRef_site.type,
NRef_site.classe,
NRef_site.img,
NRef_site.cat,
NRef_site.cp,
NRef_site.chien,
NRef_site.wifi,
NRef_site.piscine,
NRef_site.prix_repas1,
NRef_site.type_annonce,
NRef_site.handicape,
NRef_site.selec2,
NRef_site.acroche_fr,
NRef_site.nbs_chambre,
NRef_site.nbs_place_sql,
(6366*acos(cos(radians(46.683331))*cos(radians(NRef_site.x))*cos(radians(NRef_site.y)-radians(-1.766670))+sin(radians(46.683331))*sin(radians(NRef_site.x)))) AS Proximite
FROM NRef_site 
WHERE prestation_annonce.prestation IN (150)
AND EXISTS (
	SELECT 1
	FROM prestation_annonce
	WHERE prestation_annonce.annonce = NRef_site.id
	AND (prestation_annonce.date_fin='0000-00-00 00:00:00' OR prestation_annonce.date_fin>='2014-01-06 14:48:37')
)
AND NRef_site.actif=1
AND NRef_site.selec=1 AND NRef_site.type_annonce='location' AND NRef_site.x!='' AND NRef_site.selec2=3
AND (6366*acos(cos(radians(46.683331))*cos(radians(NRef_site.x))*cos(radians(NRef_site.y)-radians(-1.766670))+sin(radians(46.683331))*sin(radians(NRef_site.x))))
	< 100
ORDER BY NRef_site.star DESC, Proximite ASC
LIMIT 80;

C'est à tester, je ne garantis pas avoir bon de tête du premier coup et nous n'avons pas de jeu de test ni le schéma de vos tables, mais ça devrait donner sans doute un résultat un peu plus cohérent in fine.

Autre conseil : lisez quelques bons tutos sur le SQL.
ced

**bugbug** · 09/01/2014, 18h01

Bonjour

Merci à tous les deux pour vos réponses.
J'avoue que c'est des choses qui me dépassent et que j'ai du mal à suivre.
Oui j'ai bien indexe les champs donné par m4riachi mais quand j'aurai résolu cette requete je verrai avec vous concernant les index multi colonne.

Je vient de tester ta requete modifiée (merci) et elle me renvoie

Code :

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

Unknown column 'prestation_annonce.prestation' in 'where clause'

je vient d emodifier la requete par

Code :

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

... FROM NRef_site, prestation_annonce ...

La la requete fonctionne mais me renvoie 80 lignes identiques

**bugbug** · 09/01/2014, 18h08

Structure des 2 tables

prestation_annonce

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE IF NOT EXISTS `prestation_annonce` (
  `id` int(9) unsigned NOT NULL AUTO_INCREMENT,
  `annonce` smallint(5) unsigned NOT NULL,
  `prestation` tinyint(3) unsigned NOT NULL,
  `date_fin` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `annonce` (`annonce`),
  KEY `prestation` (`prestation`),
  KEY `date_fin` (`date_fin`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=155727 ;

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
CREATE TABLE IF NOT EXISTS `NRef_site` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `x` float(10,6) NOT NULL,
  `y` float(10,6) NOT NULL,
  `acroche_fr` tinytext NOT NULL,
  `type_annonce` varchar(20) NOT NULL DEFAULT 'location',
   `nom_fr` tinytext NOT NULL,
   `actif` tinyint(4) NOT NULL DEFAULT '0',
  `cat` int(6) unsigned NOT NULL DEFAULT '0',
   `star` tinyint(4) unsigned NOT NULL DEFAULT '0',
  `ville_sql` text NOT NULL,
  `nbs_place_sql` int(11) NOT NULL DEFAULT '0',
  `nbs_chambre` char(2) NOT NULL DEFAULT '',
   `type` text NOT NULL,
  `selec` tinyint(4) NOT NULL DEFAULT '1',
  `selec2` tinyint(2) NOT NULL DEFAULT '0',
  `cp` varchar(11) NOT NULL DEFAULT '0',
  `wifi` smallint(6) NOT NULL DEFAULT '0',
  `piscine` int(1) NOT NULL DEFAULT '0',
  `chien` int(1) NOT NULL DEFAULT '0',
   `affich_com` char(1) NOT NULL DEFAULT '1',
   `handicape` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `cat` (`cat`),
  KEY `star` (`star`),
  KEY `type_annonce` (`type_annonce`,`actif`,`selec`,`selec2`),
  KEY `x` (`x`,`y`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19564 ;

**ced** · 10/01/2014, 08h39

Oups, au temps pour moi, j'ai oublié de repasser la condition sur prestation_annonce dans la sous-requête EXISTS.
Voici une version qui devrait mieux fonctionner :

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
SELECT NRef_site.id,
NRef_site.nom_fr,
NRef_site.x,
NRef_site.y,
NRef_site.position_approx,
NRef_site.img,
NRef_site.root,
NRef_site.langue,
NRef_site.star,
NRef_site.ville_sql,
NRef_site.type,
NRef_site.classe,
NRef_site.img,
NRef_site.cat,
NRef_site.cp,
NRef_site.chien,
NRef_site.wifi,
NRef_site.piscine,
NRef_site.prix_repas1,
NRef_site.type_annonce,
NRef_site.handicape,
NRef_site.selec2,
NRef_site.acroche_fr,
NRef_site.nbs_chambre,
NRef_site.nbs_place_sql,
(6366*acos(cos(radians(46.683331))*cos(radians(NRef_site.x))*cos(radians(NRef_site.y)-radians(-1.766670))+sin(radians(46.683331))*sin(radians(NRef_site.x)))) AS Proximite
FROM NRef_site 
WHERE EXISTS (
	SELECT 1
	FROM prestation_annonce
	WHERE prestation_annonce.annonce = NRef_site.id
	AND prestation_annonce.prestation IN (150)
	AND (prestation_annonce.date_fin='0000-00-00 00:00:00' OR prestation_annonce.date_fin>='2014-01-06 14:48:37')
)
AND NRef_site.actif=1
AND NRef_site.selec=1 AND NRef_site.type_annonce='location' AND NRef_site.x!='' AND NRef_site.selec2=3
AND (6366*acos(cos(radians(46.683331))*cos(radians(NRef_site.x))*cos(radians(NRef_site.y)-radians(-1.766670))+sin(radians(46.683331))*sin(radians(NRef_site.x))))
	< 100
ORDER BY NRef_site.star DESC, Proximite ASC
LIMIT 80;

**bugbug** · 10/01/2014, 13h12

Merci SCed, ta requête fonctionne au poil, la charge du serveur a l'air d'avoir baissé de 20%, à suivre.

Par contre quand je fais un explain devant ta nouvelle requête , voila le résultat :

Code :

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

1
2
3
4
5
id select_type table type possible_keys key key_len ref rows Extra
 
1 PRIMARY NRef_site ref ville_avec_cri ville_avec_cri 25 const,const,const,const 553 Using where; Using filesort
 
2 DEPENDENT SUBQUERY prestation_annonce ref annonce annonce 3 sefaired_annuaire.NRef_site.id,const 1 Using where; Using index

Il en ressort que la requête analyse 553 lignes pour la table NRef_site alors que j'ai en moyenne 10 à 15 résultats finaux.

Voici les index actuellement utilisés sur nref_site :

ville_avec_cri : index multicolonne btree unique non : type_annonce | actif | selec | selec2

Comment puis-je optimiser ça pour que la requête analyse beaucoup de lignes ?

merci

Requêtes très lentes faisant planter le serveur

Requêtes MySQL

Discussions similaires

Partager

Partager