Problème d'optimisation de requêtes

Fermé
Signaler
Messages postés
7
Date d'inscription
mardi 18 février 2014
Statut
Membre
Dernière intervention
10 mai 2016
-
Messages postés
4270
Date d'inscription
samedi 8 septembre 2007
Statut
Membre
Dernière intervention
4 mars 2021
-
Bonjour

Alors voilà je n'arrive pas à optimiser certaines de mes requêtes et je vais vous donner l'exemple avec l'une d'entre elle.

Tout d'abord voici un aperçu de mes tables concernées

t_bloc
id_bloc
titre
contenu
date_modif
etc.

Clé primaire sur id_bloc
Index sur id_membre_bloc
Index combiné sur (date_modif,id_bloc)

t_taxon_bloc
id_taxon
id_bloc

Clé primaire sur (id_taxon, id_bloc)

t_taxon
id_taxon
titre
parent_id

Clé primaire sur id_taxon

La table t_taxon liste toutes les taxons du site (rubriques, tags, catégories...)
parent_id détermine si un taxon est dépendant d'un autre taxon (exemple une rubrique peut être dépendante d'une catégorie qui elle même est un taxon).

La table t_bloc correspond aux articles

La table t_taxon_bloc est la table de relation entre l'article et les taxons.
Par conséquent un article est associé au moin un taxon (une rubrique) et potentiellement à d'autres taxons (tags)
On a une relation 1-n

Voici donc une de mes requêtes qui pose problème

SELECT B.id_bloc FROM t_bloc AS B 
INNER JOIN t_taxon_bloc AS TB ON B.id_bloc=TB.id_bloc
WHERE TB.id_taxon=44
ORDER BY B.date_modif DESC
LIMIT 0,10

44 correspond à la rubrique 44. Par conséquent je souhaite afficher 10 articles classés par date de modification de la rubrique n°44

A première vue requête très simple qui ne devrait pas bouleverser mysql et pourtant lorsque je fais un EXPLAIN de cette requête j'obtiens ceci


id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 SIMPLE TB ref PRIMARY,id_bloc PRIMARY 4 const 292 Using index; Using temporary; Using filesort
1 SIMPLE B eq_ref PRIMARY,idx_idbloc_date PRIMARY 4 TB.id_bloc 1



On constate les horribles Using temporary; Using filesort et il ne veut pas non plus utiliser l'index date_modif. Et le temps d'excution est bcp trop long par rapport au nombre d'enregistrements (il n'y a que 292 articles qui correspondent aux critères parmis 2000)


D'ailleurs j'obtiens le même temps d'exécution et le même explain avec cette requête

SELECT count(*) FROM t_bloc AS B 
INNER JOIN t_taxon_bloc AS TB ON B.id_bloc=TB.id_bloc
WHERE TB.id_taxon=44

je n'arrive vraiment pas à cerner l'optimiseur

J'ai également essayer d'intégrer date_modif dans le where et/ou dans les champs à afficher mais rien n'a faire.

Par contre dès que j'enlève le order by dans la 1ere requête tout est parfait le explain et le temps d'exécution.

Mysql ne semble pas arriver à optimiser le order by pourtant des plus basiques.

Merci d'avance à tous ceux qui prendront de leur temps pour m'aider ;)




4 réponses

Messages postés
4270
Date d'inscription
samedi 8 septembre 2007
Statut
Membre
Dernière intervention
4 mars 2021
226
salut,
tu peux forcer l'index


FROM t_bloc AS B FORCE INDEX (date_modif,id_bloc)

et juste comme ca, inner join est implicite au join en mysql, et
limit 10
sera suffisant ;)


naga

edit : (date_modif,id_bloc) sera à remplacer par le nom de l'index si je ne m'abuse (que tu n'as pas donné)
0
Messages postés
7
Date d'inscription
mardi 18 février 2014
Statut
Membre
Dernière intervention
10 mai 2016

Bonjour

Tout d'abord merci pour avoir répondu.

Alors très honnêtement je ne suis pas fan du forçage d'index, si l'optimiseur ne l'utilise pas c'est qu'il doit y avoir un soucis dans la structure de la requête qui empêche son utilisation.
De plus même si la forcer peut fonctionner dans les tests, en production et en fonction du nombre d'enregistrement je pourrais rencontrer de gros soucis ;)
Je suis également pas fan du STRAIGHT_JOIN pour les mêmes raisons. Et je voudrais éviter également les requêtes imbriquées qui a mon avis n'aurait pas de sens pour une requête aussi simple.

Généralement je préfère me fier aux choix de l'optimiseur ;)

Donc effectivement je gagne un peu en rapidité avec le FORCE INDEX (3 à 4 fois plus rapide)

SELECT B.date_modif FROM t_bloc AS B FORCE INDEX (idx_date_idbloc) 
INNER JOIN t_taxon_bloc AS TB
ON (TB.id_bloc=B.id_bloc)
WHERE TB.id_taxon=44
order by B.date_modif DESC
limit 0,10

et je n'ai plus les USE TEMPORARY et FILESORT,

Mais je craint le pire en prod par la suite ;)

Je suis vraiment pas chaud par le FORCE INDEX désolé :/

En fait il faudrait presque pouvoir faire un index combiné de (id_taxon,date_modif) si j'ai bien compris le fonctionnement de l'optimiseur.
Mais le probleme c'est qu'ils appartiennent à 2 tables différentes :/

Mais c'est quand même dingue qu'une jointure de base n'arrive pas à s'optimiser.

par contre évidemment dès que je retire le order by tout est ok, le explain et le temps d'exécution.

Pour le limit j'ai besoin de le définir de la sorte pour la gestion de la pagination ;)
0
Messages postés
4270
Date d'inscription
samedi 8 septembre 2007
Statut
Membre
Dernière intervention
4 mars 2021
226
L'optimiseur n'est pas top dans tous les cas, ca reste un algo générique qui, pour une majeur partie des cas, permettra d'obtenir le choix le plus judicieux (optimisé donc). Mais comme tout élément générique, ils ne peux pas être fonctionnel dans 100% des cas. S'appuyer totalement/aveuglement sur l'optimiseur est une erreur (un peu comme si je te disais que tu peux à 100% te fier à windows, ou un linux, quelque soit ton matériel => l'os se veut multi-plateforme mais tu peux te retrouver en face de cas).

Pour ma part, j'utilise en prod le force index car je suis justement confronté à certains cas => je n'ai eu aucuns soucis de concordance de données ou quoi que ce soit, par contre mes temps de traitement sont bien meilleur (à hauteur de 80% de gain de temps et donc moins de charge sur le serveur).

Après, bien sûr forcer l'index reste quelquechose que l'on va faire rarement, mais parfois il faut un peu guider ton requêteur pour qu'il prenne le bon chemin :)


faire un indexe combiné revient simplement à faire une table de concordance (que tu indexera) ou encore une table temporaire mais je pense pas que ca soit terrible dans ton cas.

naga
0
Messages postés
7
Date d'inscription
mardi 18 février 2014
Statut
Membre
Dernière intervention
10 mai 2016

Je te remercies, malheureusement dans mon cas les gains gagnés (3 a 4 fois plus rapide) ne sont à mon avis pas encore suffisant. Etant donné le peu d'enregistrement dans ma phase de test je devrais être au moins 10 fois plus rapide voir plus ;)

On m'avait proposé une solution suivante sur un autre forum :

SELECT B.id_bloc  
FROM t_bloc AS B
WHERE EXISTS (
SELECT 1 FROM t_taxon_bloc AS TB
WHERE TB.id_taxon=44 AND TB.id_bloc=B.id_bloc
)
ORDER BY B.date_modif DESC

Ce qui semble donné le même résultat que le FORCE INDEX.
Là pareil j'étais moyennement chaud sur la requete imbriquée, et difficile de voir si les gains ne vont pas se transformer en perte en prod.
D'autant plus qu'il est possible qu'en fin de compte je sois obligé ensuite de passer de EXISTS à IN tout dépendra du nbr d'enregistrements de la sous requete.

Que conseillerais tu le plus, forcer l'index ou la requête imbriquée avec EXISTS?

En fait il semblerait que ce qui perturbe dans ma requête principale c'est le WHERE TB.id_taxon=44, qui pertube mon order by date et empêche l'utilisation de l'index :/

Si j'ai bien compris la requête imbriquée dissocie la requête principale de celle imbriquée ce qui fait qu'il ne reste plus qu'id_bloc et date_modif dans la requête principal d'où l'utilisation de l'index combiné.

J'avais vraiment espéré qu'en ajoutant une petite clause dans le WHERE j'aurais réglé le soucis mais j'ai pas trouvé en tout cas.

je suis peut être condamné à utiliser la requête imbriquée, le force index (même si les gains ne sont pas encore suffisant) ou a avoir des requêtes trop gourmandes :/
0
Messages postés
7
Date d'inscription
mardi 18 février 2014
Statut
Membre
Dernière intervention
10 mai 2016

Alors en fait en effectuant d'autres tests avec le force index ou la requête imbriquée, lorsque j'avais que 200 enregistrements répondant aux critères c'était moins performant (seulement 3-4 dois plus rapide) que lorsque j'ai plus de 1000 enregistrements (10 fois plus rapide).

Par conséquent il semblerait que forcer l'index ou utiliser la requête imbriquée soit vraiment plus performant que ma requête de base lorsque j'ai plus d'enregistrements. Difficile encore de comprendre pourquoi :)
0
Messages postés
4270
Date d'inscription
samedi 8 septembre 2007
Statut
Membre
Dernière intervention
4 mars 2021
226
salut,
tu as probablement déjà donné la raison : l'order by. Je connais pas le fond de mySQL, je suis juste utilisateur, mais j'ai aussi remarqué ce genre de chose (une fois que j'ai connu le force index, ce qui n'était pas le cas au départ^^). L'optimiseur connait quelques lacunes (ce qui est normal vu qu'il se veut multi schéma) et il faut donc parfois l'aiguiller un peu.

Pour tester, il faudrai que tu mette ta requête en sous requête, sans l'order by qui sera fait avec la requête mère :

select *
from (
-- TA REQUETE
) as sub
order by sub.date_modif DESC

Si tu joue la requête, je pense que cette fois l'index choisi sera le bon (histoire de voir si le soucis est sur l'order by ou non).
0