Requête SQL bloquante

[kirato]

Bonsoir à tous ! Avant toute chose : bonne année 2007 ! Espérons qu'elle soit pleine de surprises et que j'ai plus de problèmes avec mysql >_<

Pour en venir à mon problème, j'ai une requête SQL assez lourde (elle porte sur des tables déjà bien remplies ), qui mêle jointures et requêtes imbriquées. Mon soucis étant qu'elle est tellement conséquente qu'elle me sature complètement ma connexion à la base, et me bloque donc le serveur mysql (du moins pour le login que j'utilise).

Je me demandais donc si des esprits avisés pouvaient m'aider sur ce coup, ça fait 2 soirées que je m'arrache les cheveux dessus

Bon je vous met la requête en question (c'est moche désolé) :

SELECT DISTINCT p1.id_produit, p1.reference, p1.date_parution
FROM ab.produit p1
LEFT OUTER JOIN ab.piste p ON p.id_produit = p1.id_produit
WHERE p.num_support != -1
AND p1.id_produit NOT IN
  (SELECT DISTINCT s.code_barre
  FROM ab_scpp.scpp_support s
  INNER JOIN ab_scpp.scpp_lien_support sls ON sls.code_barre=s.code_barre
  GROUP BY s.code_barre, nb_support
  HAVING COUNT(DISTINCT num_support) = nb_support 
OR nb_support IS NULL )
AND p1.id_label NOT IN
  (SELECT m.id_label FROM ab_scpp.scpp_marque m
  INNER JOIN ab_scpp.fournisseurs f ON f.id_fournisseur=m.id_fournisseur
  WHERE f.mandat='N')
LIMIT 10 OFFSET 0

Avec le limit à 10 la requête passe ... dès que j'augmente aux alentours de 100 la requête commence à mettre 30s à s'exécuter :/ Je vous livre le EXPLAIN avec :

id select_type table type possible_keys key key_len ref rows Extra

1 PRIMARY p index NULL PRIMARY 15 NULL 57024 Using where; Using index; Using temporary

1 PRIMARY p1 eq_ref PRIMARY PRIMARY 13 ab.p.id_produit 1 Using where

3 DEPENDENT SUBQUERY m ref_or_null index_label index_label 3 func 4 Using where

3 DEPENDENT SUBQUERY f eq_ref PRIMARY PRIMARY 4 ab_scpp.m.id_fournisseur 1 Using where

2 DEPENDENT SUBQUERY sls index PRIMARY PRIMARY 19 NULL 47150 Using index; Using temporary; Using filesort

2 DEPENDENT SUBQUERY s eq_ref PRIMARY PRIMARY 13 ab_scpp.sls.code_barre 1

Comme vous le voyez, c'est la sous-requête comportant un GROUP BY qui fait très mal ... ma question serait de savoir s'il est possible d'améliorer la requête ou si je dois me contenter de faire un traitement php sur les données récupérées de 2 requêtes sql bien séparées ?

Si vous avez besoin d'autres précisions il ne faut pas hésiter à me demander ! (pour info je tourne sur du mysql 5.0.27)

Merci beaucoup d'avance pour votre aide

[Portekoi]

Bonjour,

Le mieux, je pense, serait de faire les deux requête suivantes "en amont" :

SELECT DISTINCT s.code_barre
  FROM ab_scpp.scpp_support s
  INNER JOIN ab_scpp.scpp_lien_support sls ON sls.code_barre=s.code_barre
  GROUP BY s.code_barre, nb_support
  HAVING COUNT(DISTINCT num_support) = nb_support OR nb_support IS NULL

Et

SELECT m.id_label FROM ab_scpp.scpp_marque m
  INNER JOIN ab_scpp.fournisseurs f ON f.id_fournisseur=m.id_fournisseur
  WHERE f.mandat='N'

Ensuite, récupère les deux chaines dans un tableau, fais un explode puis un replace pour obtenir quelque choses de la forme :

SELECT DISTINCT p1.id_produit, p1.reference, p1.date_parution
FROM ab.produit p1
LEFT OUTER JOIN ab.piste p ON p.id_produit = p1.id_produit
WHERE p.num_support != -1 AND p1.id_produit NOT IN ('".$chaine1."') AND p1.id_label NOT IN ('".$chaine2."')
LIMIT 10   OFFSET 0

A voir

Portekoi

[robinsonvendredi]

pour info je tourne sur du mysql 5.0.27

Dans ce cas enveloppe ta requete dans une procédure stockée, le plan de requête sera pré-compilé et tu auras de meilleurs résultats.

A ta place j'utiliserai aussi une table temporaire pour éviter les jointures multiples :

1.Création de la table temporaire :

SELECT DISTINCT p1.id_produit, p1.reference, p1.date_parution....INTO #stack

2.Suppression des lignes correspondant à la sous-requête 1

3.Suppression des lignes correspondant à la sous-requête 2

C'est un peu le même principe que la solution de Portekoi mais en restant en SQL

[kirato]

Merci à vous 2 J'ai uniquement testé l'utilisation d'une table temporaire, ça donne ça comme requête (pas trop compris le #stack désolé, j'ai fait à ma sauce ) :

SELECT DISTINCT p1.id_produit, p1.reference, p1.date_parution
FROM ab.produit p1
LEFT OUTER JOIN ab.piste p ON p.id_produit = p1.id_produit
LEFT OUTER JOIN
  (SELECT DISTINCT s.code_barre
  FROM ab_scpp.scpp_support s
  INNER JOIN ab_scpp.scpp_lien_support sls ON sls.code_barre=s.code_barre
  GROUP BY s.code_barre, nb_support
  HAVING COUNT(DISTINCT num_support) = nb_support OR nb_support IS NULL)
  AS tempo
  ON p1.id_produit = tempo.code_barre
WHERE tempo.code_barre IS NULL
AND p.num_support != -1
AND p1.id_label NOT IN
  (SELECT m.id_label FROM ab_scpp.scpp_marque m
  INNER JOIN ab_scpp.fournisseurs f ON f.id_fournisseur=m.id_fournisseur
  WHERE f.mandat='N')

Bon c'est pas super génial, ça s'exécute en 47s, mais au moins ça s'arrête au bout d'un moment Je vais quand même tester en passant par php pour voir si c'est pas plus rapide :]

Modifié 3 Janvier 2007 par kirato

[Portekoi]

Re,

A mon avis, cela sera beaucoup plus rapide en php car il n'y aura aucune sous requête.

Pour info, un nom de table commençant pas '#' est une table temporaire. Une table qui n'existera que le temps de l'exécution de la procédure stockée.

A bientôt

Portekoi