Discussion :

[jcmartin01]

Bonjour,
j'ai une requete assez lourde à optimiser. La version simple marche correctement, mais l'ajout de la clause "et une entrée dans la table planning" me pose pb car la table en question fait 4 millions de lignes.
Je vous présente ma requete (qui dure 10 secondes, je voudrais arriver à < 1 seconde)

En voici la version simple :

Code :

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

1
2
3
4
SELECT * FROM ex 
JOIN AAA use index (VALIDE) on AAA.valide='1' 
join RRR on AAA.truc = RRR.truc AND RRR.chose='123456' 
ORDER BY RRR.bidule, AAA.NOM, AAA.PRENOM

'ex' est une table avec une seule colonne (id) et un seul enregistrement. Elle me permet simplement de faire mes restrictions au plus vite et non pas dans la clause where comme j'aurais pu le faire :

Code :

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

1
2
3
4
SELECT * FROM AAA
join RRR on AAA.truc = RRR.truc AND RRR.chose='123456' 
where  AAA.valide='1' 
ORDER BY RRR.bidule, AAA.NOM, AAA.PRENOM

Cette version marche correctement, meme si les tables AAA et RRR contiennent chacune 4000 lignes.

Je dois maintenant m'assurer que chaque AAA a une correspondance dans planning (jointure sur la colonne code)

Ma table planning contient 4 millions d'enregistrements...
J'ai ajouté une jointure sur une sous table et non pas directemente sur planning en espérant minimiser la requete

Code :

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

1
2
3
4
5
join (select planning.code
       from ex 
       join planning on year(planning.DATEJOUR)='2010' 
       group by planning.code) planning 
on planning.code = AAA.code

Pour une requete qui ressemble finalement à ceci

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT * 
FROM ex 
JOIN AAA use index (VALIDE) on AAA.valide='1' 
join RRR on AAA.truc = RRR.truc AND RRR.chose='123456' 
join (select planning.code
       from ex 
       join planning use index (CODE) on year(planning.DATEJOUR)='2010' 
       group by planning.code) planning 
on planning.code = AAA.code 
ORDER BY RRR.bidule, AAA.NOM, AAA.PRENOM

et bon... tout cela prend encore 10 secondes à s'executer. Comment arriver à 1 seconde ?

• L'utilisation de "exists(un truc dans planning)" a donné des résultats catastrophiques.
• Le passage par la table ex me semble vraiment interessant
• J'ai un index sur planning.code
• Toutes les colonnes de planning ont une taille fixe (pas de varchar)
• J'ai fait un optimize sur planning mais ca plutot agravé les choses
• Je suis en InnoDb
• Je fais la jointure avec la sous table au dernier moment, lorsque j'ai réduit au maximum le nombre de AAA (parce que bon, tant qu'à faire une jointure sur 4M de lignes, autant la faire le moins de fois possible)

Voila voila, si vous avez des astuces suppémentaires...
Merci déjà de m'avoir lu
JC

[GyZmoO]

Salut.

Un bon début serait de nous montrer ce que donne le

EXPLAIN

correspondant à la requête ! (Voir ce que MySQL fabrique et comment il fait.)

La structure de la table aussi serait pas mal !

@+

[Edit] J'ai pas bien bien regardé la requête, mais as-tu un index déclaré sur les 3 colonnes qui entrent en jeu dans le ORDER BY ?[/Edit]

[CinePhil]

Si tu mélanges condition de jointure (ON) et condition de restriction (WHERE), peut-être que ça n'aide pas le SGBD à bien faire son travail !

Le cas typique où une condition de restriction doit figurer dans la condition de jointure, c'est quand tu dois faire une restriction sur les données de la table en jointure externe.

Ensuite, faire une table d'une colonne et d'une seule ligne pour l'utiliser en jointure au lieu d'utiliser une condition de restriction classique dans un WHERE, j'ai des doutes sur le fait que ça puisse améliorer les performances ! Une table de plus en mémoire, fut-elle petite, une jointure de plus à faire, même simple, ça surcharge quand même le SGBD.

Pendant qu'on y est, évite donc de lancer dans la guerre des étoiles !

Si, comme le laissent supposer ton exemple, les valeurs souhaitées sont de type numérique, inutile de les encadrer par des apostrophes !

Ta première requête devrait donc plutôt être de cette forme :

Code :

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

1
2
3
4
5
6
SELECT les_colonnes_dont_tu_as_besoin
FROM AAA
JOIN RRR ON AAA.truc = RRR.truc 
WHERE  AAA.valide = 1
    AND AND RRR.chose = 123456 
ORDER BY RRR.bidule, AAA.NOM, AAA.PRENOM

Dans ta sous requête, tu fais un GROUP BY sans utiliser de fonction d'aggrégation (SUM, AVG, MIN, MAX, COUNT). C'est inutile !

De même que précédemment, la fonction YEAR retourne un entier donc inutile d'entourer la valeur par des apostrophes !

Ta requête finale pourrait ressembler plutôt à ceci :

Code :

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

1
2
3
4
5
6
7
8
SELECT les_colonnes_dont_tu_as_besoin
FROM AAA
INNER JOIN RRR ON AAA.truc = RRR.truc 
INNER JOIN planning ON planning.code = AAA.code
WHERE  AAA.valide = 1
    AND RRR.chose = 123456 
    AND year(planning.DATEJOUR) = 2010
ORDER BY RRR.bidule, AAA.NOM, AAA.PRENOM

Si le GROUP BY était destiné à éviter d'avoir plusieurs lignes pour l'année 2010, utilise plutôt le DISTINCT, ce qui donne au final ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT les_colonnes_dont_tu_as_besoin
FROM AAA
INNER JOIN RRR ON AAA.truc = RRR.truc 
INNER JOIN (
    SELECT DISTINCT code
    FROM planning 
    WHERE year(planning.DATEJOUR) = 2010
) AS t ON t.code = AAA.code
WHERE  AAA.valide = 1
    AND RRR.chose = 123456 
ORDER BY RRR.bidule, AAA.NOM, AAA.PRENOM

Pour des performances améliorées, il faut bien sûr que les colonnes truc et code soient indexées (conditions de jointure), ainsi que la colonne chose.