Discussion :

[Gaetch]

Bonjour,

J'ai une requête sur deux tables (en fait plus que ça mais je vais simplifier) qui fait la chose suivante : on a une table d'historique de mouvements (entrées, sorties, etc.) et une table de produits. On veut chercher dans l'historique les produits selon un certain critère (critère se trouvant dans la fiche produit) mais trier le résultat par date (date de l'historique, en fait on utilise l'id de la table) puis renvoyer les 1000 premiers résultats (on ne veut pas remonter trop de lignes à l'appli). On a donc 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
SELECT
    *
FROM
    historique H
    LEFT OUTER JOIN produit P USING (reference)
WHERE
    P.famille = famille_choisie
    AND H.date between date_debut AND date_fin
ORDER BY
    H.id DESC
LIMIT
    1000

En faisant un EXPLAIN, MySQL me dit "Using Filesort" et la requête est vraiment extrêmement longue. J'ai un index sur la famille, la référence et l'id. Que puis-je faire ?

[qi130]

Indexer la date.

[Fabllot]

Bonjour,

Indexer la date.

Effectivement, le traitement sur des types non entiers est toujours plus long...

De plus il est préférable de faire une jointure sur "reference" si ce dernier est aussi un entier (éviter ici les chaines de caractères).
Après j'ai moi aussi remarqué que MySql devient un peu lent dès qu'on s'amuse avec les jointures sur des tables de plusieurs (dizaines de) milliers d'éléments.
Donc si c'est ton cas et que tes références de tes produits que tu veux ne sont pas trop nombreuses tu as interêt à faire ta requête en deux étapes :

1. 1 tu récupères tes références de produits
2. 2 tu récupères ton historique

Autre point, il serait conseillé de ne pas faire des SELECT * mais spécifier les champs que tu souhaites... Cependant je n'ai jamais réellement testé pour voir s'il y a une différence notable.

[Gaetch]

Ma date est (évidemment) indexée.

Entier ou chaîne pour la jointure (référence), les performances ne changent pas pour cette requête j'ai tenté les deux comme la référence était initialement une chaîne.

J'ai également tenté de faire le tout en deux étapes, avec une requête interne, comme avec une table temporaire. J'ai même essayé de poser des index sur la table temporaires. Rien n'y fait.

Pour info, la table historique compte plusieurs millions de lignes.

Au final, j'ai ajouté un champ "famille" dans l'historique, et posé un index sur famille+date, c'est très efficace mais je trouve la solution un peu dégueulasse...

[CinePhil]

Code :

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

1
2
3
FROM
    historique H
    LEFT OUTER JOIN produit P USING (reference)

Ne vaudrait-il mieux pas faire l'inverse ?

Code :

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

1
2
FROM produit P 
    LEFT OUTER JOIN historique H USING (reference)

Voir même une jointure interne puisque en principe tous les produits figurant dans l'historique sont dans la table produit non ?

[Gaetch]

Non, on prévoit le cas où un produit est supprimé de leur référentiel.

[Fabllot]

Au final, j'ai ajouté un champ "famille" dans l'historique, et posé un index sur famille+date, c'est très efficace mais je trouve la solution un peu dégueulasse...

C'est un point de vue.
Les "optimisations" SQL classiques théoriques t'optimisent la taille de tes bases de données sur le disque, mais pas forcément le temps d'exécutions des requêtes.
Aujourd'hui les tailles des bases de données sur disque ne sont généralement pas un problème (les disques de plusieurs tera ne sont pas très chers), par contre les vitesses d'accès sont primordiales et les processeurs et la mémoire vive sont chères sans gain évident.

Alors si tu optimises tes tables pour un accès plus rapide tout en perdant sur leurs tailles, je ne trouve pas que ce soit dégueulasse...

Après c'est mon point de vue, purement personnel...

[CinePhil]

Je viens de relire ton premier message et je me rends compte qu'en fait, ta jointure externe devient une jointure interne puisque tu as une condition dans le WHERE qui porte sur la table externe (P.famille = famille_choisie) comme je l'explique dans mon blog.

Peut-être que si tu rapatrie la condition de restriction dans la jointure, cela va réduire le temps puisqu'il y aura moins de lignes à joindre ?

[Waldar]

Non, on prévoit le cas où un produit est supprimé de leur référentiel.

Il vaut mieux gérer ces cas de figure avec une suppression logique (un flag inactif et une date associée par exemple).