[Résolu] Optimisation requête SQL

Salut à tous,

Je coince sur une requête SQL qui est peut être trop lourde, et dont je n'arrive pas à obtenir ce que je souhaite.

Ci-dessous, le détail de ma base SQL.



Ci-dessous la jointure que j'essaye de faire qui dure très très longtemps.

SELECT
n.id,
n.reference,
COUNT(abe.id) AS additionalbomelement,
COUNT(af.id) AS additionalfastener,
COUNT(bfps.id) AS bomfromps,
COUNT(b.id) AS bonding,
COUNT(c.id) AS coilup,
COUNT(mb.id) AS manualbonding,
COUNT(mp.id) AS mountingprinciple,
COUNT(sno3dg.id) AS specificnoteon3dgeometry,
COUNT(sn.id) AS standardnote,
COUNT(tb.id) AS titleblock,
COUNT(tbg.id) AS totalbonding

FROM
nodes AS n

LEFT JOIN
	param_additionalbomelement AS abe
		ON
		n.id = abe.ref
LEFT JOIN
	param_additionalfastener AS af
		ON
		n.id = af.ref
LEFT JOIN
	param_bomfromps AS bfps
		ON
		n.id = bfps.ref
LEFT JOIN
	param_bonding AS b
		ON
		n.id = b.ref
LEFT JOIN
	param_coilup AS c
		ON
		n.id = c.ref
LEFT JOIN
	param_manualbonding AS mb
		ON
		n.id = mb.ref
LEFT JOIN
	param_mountingprinciple AS mp
		ON
		n.id = mp.ref
LEFT JOIN
	`param_specific note on threed geometry` AS sno3dg
		ON
		n.id = sno3dg.ref
LEFT JOIN
	`param_standard note` AS sn
		ON
		n.id = sn.ref
LEFT JOIN
	param_titleblock AS tb
		ON
		n.id = tb.ref
LEFT JOIN
	param_totalbonding AS tbg
		ON
		n.id = tbg.ref

WHERE
n.type = 1

ORDER by
n.reference

Est-ce qu'il existe des méthodes d'optimisations? Car là, à part devoir calculer une première fois ce résultat, et le stocker dans la base, je ne vois pas comment obtenir quelque chose de "rapide" et fiable.

Par avance merci de votre aide !

P.S: J'ai oublié d'expliquer à quoi sert cette requête.

La table "nodes" comprend des références de produits.

Chaque produits peut être constitué d'une multitude de paramètre.
Chaque type de paramètre est une table à part entière (le nom de table paramètre commence par "param_".

Un produit peut avoir plusieurs instance d'une paramètre.

Le but de la requête est de compter, pour un certain "type" de produits (1), le nombre de paramètre de chaque type.
Modifié par Super_baloo8 (23 Feb 2013 - 18:42)

J'ai l'impression que la base est mal faite, ne serait-ce pas mieux d'avoir une table params avec ref, identifier, param et type ?

Faire des requêtes indépendantes est aussi long ?

SELECT
id,
reference,
(SELECT COUNT(*) 
   FROM nodes as n
   JOIN param_additionalbomelement as p 
     ON p.ref = nodes.id
  WHERE n.type=1
) AS additionalbomelement,
...
FROM node
WHERE type=1
ORDER BY reference

Modifié par jo_link_noir (23 Feb 2013 - 22:59)

Merci pour ton retour Jo.

Effectivement, je crois que j'ai mal construit ma base mais j'ai préféré l'avoir ainsi pour éviter d'avoir de trop grosse base (je traite des fichiers csv qui peuvent contenir 2 millions de lignes).

En supprimant la jointure, c'est beaucoup plus performant, et ça permet de traiter l'information:

SELECT
`n`.`id`,
`n`.`reference`,
(SELECT COUNT(*) FROM `param_additionalbomelement` WHERE `ref` = `n`.`id`) AS `additionalbomelement`,
(SELECT COUNT(*) FROM `param_additionalfastener` WHERE `ref` = `n`.`id`) AS `additionalfastener`,
(SELECT COUNT(*) FROM `param_bomfromps` WHERE `ref` = `n`.`id`) AS `bomfromps`,
(SELECT COUNT(*) FROM `param_bonding` WHERE `ref` = `n`.`id`) AS `bonding`,
(SELECT COUNT(*) FROM `param_coilup` WHERE `ref` = `n`.`id`) AS `coilup`,
(SELECT COUNT(*) FROM `param_manualbonding` WHERE `ref` = `n`.`id`) AS `manualbonding`,
(SELECT COUNT(*) FROM `param_mountingprinciple` WHERE `ref` = `n`.`id`) AS `mountingprinciple`,
(SELECT COUNT(*) FROM `param_specific note on threed geometry` WHERE `ref` = `n`.`id`) AS `specificnoteon3dgeometry`,
(SELECT COUNT(*) FROM `param_standard note` WHERE `ref` = `n`.`id`) AS `standardnote`,
(SELECT COUNT(*) FROM `param_titleblock` WHERE `ref` = `n`.`id`) AS `titleblock`,
(SELECT COUNT(*) FROM `param_totalbonding` WHERE `ref` = `n`.`id`) AS `totalbonding`
FROM `nodes` AS `n`
WHERE `type` = 1
ORDER BY `reference`

J'en suis environ à 30 secondes de traitements contre 5 minutes, c'est juste bluffant !

Par curiosité, je vais quand même tenter de ne jouer qu'avec une seule table pour tous les paramètres, pour voir si j'arrive à de meilleur temps.

Ensuite, c'est déjà pas mal ce temps, comme il ne s'agit que d'un "extract" à faire de temps en temps ...

Mille merci déjà =)
Modifié par Super_baloo8 (24 Feb 2013 - 01:18)

Salut,

Alors, je ne suis pas trop sûr de moi, mais, sauf erreur de ma part, tu cherches à compter les items associés à un node référence donné.
Ne serait-il pas plus efficace de maintenir une table supplémentaire dans laquelle tu stockes id, référence, le comptage des éléments associés dans chaque table, et mettre un trigger sur ajout et suppression sur les tables associées, pour qu'il mettre à jour le comptage.
Comme ça, plus de jointures et requête hyper-rapide, à mon avis.

Salut Marvin,

C'est exactement ce que je fait en ce moment, je compile les données pour les stockées dans une table (qui n'est pas affichée dans mon image).

Par contre, à part d'être toujours avec un script qui met à jours les comptages, je ne sais pas le faire directement depuis mysql ? Je ne sais pas si c'est possible d'ailleurs (fonction de comptage lors de l'insertion de donnée)

Il te suffit de créer un trigger sur chacune des tables concernées (pour peu que cette fonctionnalité soit disponible sur ton hébergement), la syntaxe est relativement simple : tu indiques juste la table, le type d'évènement sur lequel ça se déclenche (ajout ou suppression dans ton cas), et la ou les commandes à exécuter. Cherche les tutos sur des termes genre "mysql tuto trigger"; il y en a un sur le site du zéro qui couvre ça entre autres, mais je n'ai pas eu l'occasion de le lire pour juger de son contenu.
N.B. : Bien entendu, il faudra penser à peupler la table au départ avec les valeurs de départ, en s'assurant qu'il n'y a pas d'interaction extérieure avec les tables dans l'intervalle.

DOUBLON
Modifié par Super_baloo8 (02 Mar 2013 - 16:27)

Merci pour ces explications, je vois les triggers dont tu parles, mais ça ne permet pas d'effectuer un comptage par la base MySql directement

Quoiqu'il arrive, j'avais un problème d'architecture sur ma base, je suis passé sur une base avec seulement 2 tables (une pour les références, et une autre qui comprends tout le reste), et maintenant, c'est super rapide !!!

Bref, multiplier le nombre de table dans une base est une mauvaise idée, car cela consomme beaucoup de ressource, et de temps, pour pas vraiment de "plus".

Merci à tous pour votre aide !!!