Requête SQL, optimisation

Version imprimable

Bonjour à toutes et à tous,

J'ai un gros problème au niveau d'une requête qui se termine en max execution time 300 sec.

Pour faire simple je vais vous montrer mes tables (simplifié au niveau des colonnes) ainsi que la requête pour être plus clair :

Citation:

Produit :
id_produit int
description text

Citation:

Couleur_produit :
id_coul_prod int
id_produit int
id_couleur int

Citation:

Sous_produit_photo :
id_photo int
id_produit int
nom_photo text

Citation:

Sous_produit_prix :
id_coul_prod int
prix 1 decimal
prix 2 decimal

Ma requête :
Code:

1 2 3 4 5 6 7 SELECT * FROM produit LEFT OUTER JOIN sous_produit ON sous_produit.id_produit = produit.id_produit LEFT OUTER JOIN sous_produit_photo ON sous_produit_photo.id_produit = produit.id_produit LEFT OUTER JOIN sous_produit_prix ON sous_produit_prix.id_coul_prod = sous_produit.id_coul_prod AND id_fournisseur = 2 GROUP BY sous_produit.id_coul_prod
Mes jointures sont donc dans ma requêtes.

Un produit peut être présent plusieurs fois dans sous_produit et pareil dans sous_produit_photo
Un sous produit est présent que 1 fois dans sous_produit_prix

Et j'ai des milliers d'enregistrement dans la table produit et donc encore + dans sous_produit etc...

Quand j'exécute cette requête, elle plante au bout de 300 secondes en me disant l'erreur max execution time.

Je sais pas si j'ai un soucis d'optimisation ou je ne sais quoi mais sa me freine énormément dans mon développement.

Si quelqu'un pourrait m'aider ce serai génial ^^

Merci et bonne journée à vous !

17/06/2014, 15h54
punkoff

bonjour,

qu'est censé faire cette requête ? (le group by ne sert à rien si vous n'utilisez pas de fonctions d'agrégations)

Là vous ramenez beaucoup de donnée à cause des jointures avec des relations 0,n.

Il faut voir que si vous avez, pour un produit, 10 photo et 10 sous_produit cela se transformera en 100 lignes résultante.
17/06/2014, 15h55
SQLpro

Commencez par mettre des clefs à vos tables : Sous_produit_photo et Sous_produit_prix

Quels sont les index ?

Quels sont les types de données ???

Postez le DDL de vos tables pour éviter de faire perdre du temps à tout le monde !

A +
17/06/2014, 16h17
MimiWoOlf

Merci de vos réponses,

Je ne peux mettre le DDL car ce ne sont que des tables d'exemple qui synthétise les vraies tables.

Au niveau des relations elles sont dans les LEFT OUTER JOIN de la requête.

Je veux juste faire un select * en gros pour afficher :

le produit avec l'id_coul_prod lui correspondant et ses prix du genre :

id_produit = 1 description = test id_couleur = 45 id_coul_prod = 127 prix1 = 5.7 prix2 = 5.8 nom_photo = tshirt_jaune.jpg
id_produit = 1 description = test id_couleur = 46 id_coul_prod = 135 prix1 = 6.8 prix2 = 6.4 nom_photo = tshirt_rouge.jpg
17/06/2014, 16h29
aieeeuuuuu

Bonjour

Citation:

Envoyé par MimiWoOlf

Je ne peux mettre le DDL car ce ne sont que des tables d'exemple qui synthétise les vraies tables.

Et bien mettez les DDL des vraies tables

Citation:

Envoyé par MimiWoOlf

Au niveau des relations elles sont dans les LEFT OUTER JOIN de la requête.

Oui, mais justement la requête semble imparfaite...
Avec les DDL des tables, on pourrait mieux et surtout plus rapidement comprendre votre modèle et vous proposer des axes d'améliorations.
Par exemple, je vous proposerai bien de remplacer vos jointures externes par des jointures internes, mais sans connaitre votre modèle (et votre besoin), je ne peux pas savoir si cela est valable pour toutes les tables. Donc : pourquoi des jointures externes dans votre requête ?

Citation:

Envoyé par MimiWoOlf

Je veux juste faire un select * en gros pour afficher :

le produit avec l'id_coul_prod lui correspondant et ses prix du genre :

id_produit desc id_couleur id_coul_prod prix1 prix2 nom_photo

Pourquoi faire un SELECT * si vous n'utilisez au final que 7 colonnes ? spécifiez le nom des colonnes dont vous avez besoin et seulement celles-là. Bien sûr, ceci ne sera réellement efficace que si vous disposez des index nécessaires. Là encore, les DDL des table avec index seraient utiles

Citation:

Envoyé par MimiWoOlf

Et j'ai des milliers d'enregistrement dans la table produit et donc encore + dans sous_produit etc

Et quel est l’intérêt de tout récupérer ? afficher sur une seule page toutes ces informations ??? une petite description du contexte serait aussi utile...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `produit` (
 `id_produit` int(11) NOT NULL AUTO_INCREMENT,
 `id_fournisseur` int(11) NOT NULL,
 `id_marque` int(11) NOT NULL,
 `reference` varchar(80) NOT NULL,
 `reference_fournisseur` varchar(80) DEFAULT NULL,
 `nom_prod_fr` varchar(255) NOT NULL,
 `nom_prod_en` varchar(255) NOT NULL,
 `description_fr` text NOT NULL,
 `description_en` text NOT NULL,
 PRIMARY KEY (`id_produit`)
) ENGINE=MyISAM AUTO_INCREMENT=3000 DEFAULT CHARSET=utf8

Code:

1
2
3
4
5
6
7
8
9
CREATE TABLE `sous_produit` (
 `id_coul_prod` int(11) NOT NULL AUTO_INCREMENT,
 `id_produit` int(11) NOT NULL,
 `id_couleur` int(11) NOT NULL,
 `taille` varchar(80) NOT NULL,
 `sous_reference` varchar(80) NOT NULL,
 `sous_reference_fournisseur` varchar(80) NOT NULL,
 PRIMARY KEY (`id_coul_prod`)
) ENGINE=MyISAM AUTO_INCREMENT=20402 DEFAULT CHARSET=utf8 COMMENT='    '

Code:

1
2
3
4
5
6
7
8
9
CREATE TABLE `sous_produit_photo` (
 `id_photo` int(11) NOT NULL AUTO_INCREMENT,
 `id_produit` int(11) NOT NULL,
 `id_couleur` int(11) NOT NULL,
 `nom_photo` varchar(255) NOT NULL,
 `defaut` int(2) NOT NULL,
 `update_staff` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id_photo`)
) ENGINE=MyISAM AUTO_INCREMENT=7955 DEFAULT CHARSET=utf8

Code:

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
CREATE TABLE `sous_produit_prix` (
 `id_coul_prod` int(11) NOT NULL AUTO_INCREMENT,
 `qte_1` int(11) NOT NULL,
 `prix_achat_1` decimal(20,5) NOT NULL,
 `prix_vente_1` decimal(20,5) NOT NULL,
 `coef_1` decimal(20,5) NOT NULL,
 `qte_2` int(11) NOT NULL,
 `prix_achat_2` decimal(20,5) NOT NULL,
 `prix_vente_2` decimal(20,5) NOT NULL,
 `coef_2` decimal(20,5) NOT NULL DEFAULT '1.00000',
 `qte_3` int(11) NOT NULL,
 `prix_achat_3` decimal(20,5) NOT NULL,
 `prix_vente_3` decimal(20,5) NOT NULL,
 `coef_3` decimal(20,5) NOT NULL DEFAULT '1.00000',
 `qte_4` int(11) NOT NULL,
 `prix_achat_4` decimal(20,5) NOT NULL,
 `prix_vente_4` decimal(20,5) NOT NULL,
 `coef_4` decimal(20,5) NOT NULL DEFAULT '1.00000',
 `qte_5` int(11) NOT NULL,
 `prix_achat_5` decimal(20,5) NOT NULL,
 `prix_vente_5` decimal(20,5) NOT NULL,
 `coef_5` decimal(20,5) NOT NULL DEFAULT '1.00000',
 `qte_6` int(11) NOT NULL,
 `prix_achat_6` decimal(20,5) NOT NULL,
 `prix_vente_6` decimal(20,5) NOT NULL,
 `coef_6` decimal(20,5) NOT NULL DEFAULT '1.00000',
 `qte_7` int(11) NOT NULL,
 `prix_achat_7` decimal(20,5) NOT NULL,
 `prix_vente_7` decimal(20,5) NOT NULL,
 `coef_7` decimal(20,5) NOT NULL DEFAULT '1.00000',
 `qte_8` int(11) NOT NULL,
 `prix_achat_8` decimal(20,5) NOT NULL,
 `prix_vente_8` decimal(20,5) NOT NULL,
 `coef_8` decimal(20,5) NOT NULL DEFAULT '1.00000',
 `qte_9` int(11) NOT NULL,
 `prix_achat_9` decimal(20,5) NOT NULL,
 `prix_vente_9` decimal(20,5) NOT NULL,
 `coef_9` decimal(20,5) NOT NULL DEFAULT '1.00000',
 `qte_10` int(11) NOT NULL,
 `prix_achat_10` decimal(20,5) NOT NULL,
 `prix_vente_10` decimal(20,5) NOT NULL,
 `coef_10` decimal(20,5) NOT NULL DEFAULT '1.00000',
 PRIMARY KEY (`id_coul_prod`)
) ENGINE=MyISAM AUTO_INCREMENT=21809 DEFAULT CHARSET=utf8

Voila les DDL. J'ai montré juste 7 colonnes pour que vous compreniez en gros comment je veux récupérer mes résultats mais globalement c'est un select *.

Je veux récupérer tous ces éléments car je veux ensuite exporter tout ça dans un fichier CSV, donc récupérer chaque sous_produit avec les prix qui lui correspond ainsi que sa photo. J'espère que vous avez tous les éléments et désolé des oublies.

PS : Il y'a surement des incohérences de jointure à simplifier mais je ne pense pas que c'est la source de mon problème.

Bonjour,

Comme dit SQLpro, où sont les index ? A défaut, le SGBD effectuera des balayages complets des 4 tables...

Pourriez-vous procéder à un EXPLAIN de la requête et nous présenter le résultat ? On connaîtra objectivement la stratégie de MySQL :
Code:

1 2 3 4 5 6 EXPLAIN SELECT * FROM produit LEFT OUTER JOIN sous_produit ON sous_produit.id_produit = produit.id_produit LEFT OUTER JOIN sous_produit_photo ON sous_produit_photo.id_produit = produit.id_produit LEFT OUTER JOIN sous_produit_prix ON sous_produit_prix.id_coul_prod = sous_produit.id_coul_prod AND id_fournisseur = 2