Discussion :

[MichDeRoanne]

Bonjour - je cherche à optimiser cette requête (index, index bitmap, index de jointure, autre idée ?).
Pouvez-vous m'aider SVP ?
Quels index il faudrait utiliser sur des join ?

Code :

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

1
2
3
4
5
6
7
8
SELECT dept_emp.emp_no, employees.last_name, employees.first_name, departments.dept_name
FROM dept_emp
JOIN employees
ON dept_emp.emp_no = employees.emp_no
JOIN departments
ON dept_emp.dept_no = departments.dept_no
WHERE departments.dept_name = 'Sales' 
OR departments.dept_name = 'Development';

Source de la base : https://dev.mysql.com/doc/employee/e...structure.html

Ou si éventuellement, sur cette bdd, si vous avec une autre requête avec des join déjà optimisée, je suis preneur pour comprendre le principe.

[binarygirl]

Bonjour,

Si vous avez repris cette structure pour créer les tables, alors vous verrez qu'il y a déjà des index...
Avant de chercher à "optimiser", il serait bon d'évaluer le niveau de performance actuel pour déterminer ce qui est optimisable...
Je vous conseille d'étudier la commande explain et aussi de vous familiariser avec le plan d'exécution. Ce sont des notions que l'on retrouve dans les autres systèmes de bases de données.
Votre intuition est correcte: lorsqu'on fait un join entre deux tables, ça aide énormément de le faire sur des champs indexés. Le scénario que vous voulez éviter dans une requête en général est le "full table scan", autrement dit, la lecture de toute la table sans utilisation d'index.

Mais ça dépend aussi du volume de données. Si les tables ont peu d'enregistrements, alors la présence d'un index n'apporte pas grand-chose.
Donc il n'y a pas de réponse toute faite, ça dépend aussi de l'utilisation. Si vous faites des "lookups" ou joins sur d'autres champs, alors il peut s'avérer utile de les indexer aussi, voire même de revoir la structure des tables dans certains cas.

Si les index accélèrent les recherches, ils ralentissent aussi les mises à jour (insert/update) puisqu'ils doivent suivre les changements dans les données. Les index doivent donc être utilisés avec parcimonie.

Puisque vous avez un exemple concret: comptez le nombre d'enregistrements dans les tables vs le nombre d'enregistrements retournés par votre requête. Examinez le résultat de la commande explain + le temps écoulé + les index effectivement utilisés. En guise d'exercice, vous pourriez aussi répéter cette opération après avoir supprimé les index pour constater la différence de performance. Encore une fois, elle ne sera pas forcément critique si le volume de données est réduit.

[MichDeRoanne]

Super merci pour ce retour.

J'avais pas précisé, mais j'ai testé effectivement différentes possibilités en regardant à chaque fois le plan d'exécution.
Mais je n'arrive pas à l'améliorer. J'ai à chaque fois 1 ou plusieurs TABLE ACCESS \ FULL, et un COST élevé.

Je ne sais pas comment écrire un index de jointure.

[binarygirl]

Le mieux serait peut-être de poster le résultat de la commande explain, et éventuellement donner une idée du volume de données (nombre approximatif d'enregistrements par table) + la requête exacte.
Dans certains cas, on peut optimiser une requête en la ré-écrivant différemment, parfois en spécifiant soit-même les index (on contourne alors le query optimizer), mais il est aussi possible qu'il n'y a pas de marge d'amélioration dans l'état actuel des choses.
En pratique on cherche à optimiser les requêtes qui sont "lourdes", ou bien les environnements à forte charge (ex: site E-commerce), on ne se préoccupe pas trop des requêtes qui prennent quelques millisecondes à s'exécuter si le gain en ressources que l'on peut escompter est marginal.

Un bête truc mais:

Code :

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

1
2
WHERE departments.dept_name = 'Sales' 
OR departments.dept_name = 'Development';

peut être formulé ainsi:

Code :

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

WHERE departments.dept_name IN ('Sales', 'Development')

C'est un peu plus simple/lisible/flexible, en revanche je n'attends pas de gain de performance en faisant cela, mais il faut expérimenter aussi.

Je ne sais pas si vous lisez l'anglais technique mais la doc est incontournable: https://dev.mysql.com/doc/refman/8.0...imization.html
En vérifiant que la doc correspond à la version que vous utilisez.

[MichDeRoanne]

Alors pour les 2 tables 'employees' et 'dept_emp' : 300.000 enregistrements.
Et table 'departments' : 9 enregistrements.

Voilà en pdf le plan d'exécution.plan2.pdf

[JeitEmgie]

Ce sont aussi les cardinalités des employés dans chacun des départements concernés qui pourraient aider.
Cela permettrait de voir si une UNION ALL (avec un hint de parallélisme si < 12.1, sinon cela devrait être automatique) pourrait aider.

[binarygirl]

Le pdf est parfaitement illisible pour moi, je vois juste une image minuscule. Le mieux est de poster du texte formaté avec la balise code.

Au fait, je ne sais pas combien d'enregistrements renvoie la requête, mais si vous récupérez disons un tiers de ces 300000 enregistrements que contient la table, alors ça fait quand même un gros volume de données qui doit être extrait, consolidé, puis voyager via une connexion réseau probablement, et cela aussi à un coût.
On essaie en général de ne récupérer que le volume nécessaire, par exemple en filtrant sur des plages de dates ou des critères additionnels. L'utilisateur ne veut normalement pas consulter 100000 enregistrements en un coup, donc on essaie de filtrer à la source.

Un petit lien intéressant pour aller plus loin: How to Measure MySQL Query Time: A Detailed Look

Et pour avoir une idée à l'arrache du volume de trafic réseau engendré: https://serverfault.com/a/530540

Même sans aller jusque-là, vous pouvez facilement évaluer la taille de vos tables: https://chartio.com/resources/tutori...able-in-mysql/
Puis vous faire une idée approximative certes mais utile quand même. Si vous récupérez un dixième du contenu de la table, alors pour faire simple considérez que le volume de données extrait équivaut à un dixième de la taille totale de la table.

Autrement dit, si vous pompez 25 Mb de données à chaque fois (exemple), c'est normal que ce ne soit pas instantané si on tient compte de toutes les étapes, jusqu'à l'affichage des données vers le client de destination. C'est pour cela qu'il est intéressant de faire des mesures et avoir une idée même vague des ressources consommées.

Une requête "optimisée" mais qui renvoie un nombre délirant d'enregistrements n'est pas une requête rapide ou optimale.

[MichDeRoanne]

Merci encore

Voilà l'EXPLAIN PLAN en .xls

[tbc92]

Qui administre ta base de données ; A priori personne, vu qu'on parle de petits volumes.
Je pense qu'une chose à faire, c'est ceci :
analyze table xxxx compute statistics.
En répétant cette commande pour chacune des tables.
Quand on insère des données dans les tables, au fil du temps, les index peuvent devenir déséquilibrés, peu performants.
Quand une table fait 300000 lignes, il faut exécuter cette commande de temps en temps, en gros quand il y a 10% des données qui changent (Insert ou delete ou update).

Tu lances cette commande pour chaque table, puis tu essaies à nouveau ta requête. J'ai vu des cas où on passait de 10sec à 0.1sec, juste avec ces 'analyze table'. Les index étaient devenus tellement dégradés que le moteur Oracle ne les utilisait pas.

[JeitEmgie]

On est dans le forum ORACLE DB, pas MySQL : il faudrait déplacer la discussion au bon endroit si réellement vous utilisez MySQL, par contre si vous avez vraiment déployer sous ORACLE cet exemple du site MySQL,
il faudrait aussi donnez la version de votre DB et la description complète des tables concernées.

[SQLpro]

Vérifiez que vous avez bien les index suivants :

Code :

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

1
2
3
X1 : dept_emp (dept_no)
X2 : employees (emp_no, last_name, first_name)
X3 : departments (dept_name, dept_no)

Si tel n'est pas le cas, créées les.

Il est dommage qu'oracle, toujours très en retard techniquement, n'implémente pas la clause INCLUDE auquel cas il aurait été préférables pour alléger le coût des mises à jour d'avoir les index suvants :

Code :

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

1
2
3
X1 : dept_emp (dept_no)
X2 : employees (emp_no) INCLUDE (last_name, first_name)
X3 : departments (dept_name) INCLUDE (dept_no)

Comme le fait Microsoft SQL Server depuis 18 ans ou PostGreSQL depuis 2/3 ans.

A +