Discussion :

[Matth_S]

Bonjour,

Je cherche a optimiser certaines requetes que je retrouve dans le log des slow query.
J'ai une requete en particulier qui me pose probleme.
Il s'agit d'une requete sur 3 tables :
recherche,abonnements,coord_recherche :
-la table recherche regroupe le nom,prénom des individus
-la table coord_recherche les informations telles que adresse,ville,pays
-la table abonnements contient les informations sur leur abonnements (type d'abonnements, validation, rubrique de l'abonnement etc).

la requete en question est

Code :

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

1
2
3
4
5
6
7
 
SELECT DISTINCT id_rubrique, COUNT( abonnements.id_abonnement ) AS compte
FROM abonnements JOIN recherche on abonnements.id_abonnement=
recherche.id_abonnement JOIN coord_recherche on coord_recherche.id_abonnement=abonnements.id_abonnement
WHERE coord_recherche.country =1
AND online =1
GROUP BY id_rubrique;

Elle a pour but de retourner le nombre d'abonnés correspondant aux criteres de recherche (ici il s'agit d'une recherche sur le pays) par rubrique.

La table mise en jeu sont pourtant correctement indexé :
index sur id_abonnement dans recherche et coord_recherche (puisqu'il s'agit d'une clé étrangère).
index sur country dans la table coord_recherche.

Pour y voir plus clair , j'ai fait un EXPLAIN de cette requete et je vois que mes index sur la table coord_recherche sont ignorés :

Code :

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

1
2
3
4
5
 
table  type  possible_keys  key  key_len  ref  rows  Extra  
coord_recherche ALL country,id_abonnement NULL NULL NULL 70389 Using where; Using temporary; Using filesort 
abonnements eq_ref PRIMARY PRIMARY 4 coord_recherche.id_abonnement 1 Using where 
recherche ref id_abonnement id_abonnement 5 abonnements.id_abonnement 1 Using where; Using index

Voici le résultat du EXPLAIN
La table coord_recherche est parcouru dans son intégralité alors que 2 clés sont possibles et aucunes n'est utilisée.

Pouvez vous m'expliquer ce qui amene MySQL a ignorer ces index ? Que puis-je faire pour optimiser cette requete (son temps d'execution est pour le moment de 5 sec).

[Adjanakis]

Bonjour,

Serait-il possible d'avoir le résultat de la requête EXPLAIN en remplaçant le ';' en fin de requête par un '\G'(si l'utilitaire en ligne de commande est utilisé). La c'est un peu trop illisible.

[Matth_S]

Je n'ai pas lancé la requete en ligne de commande (je l'exectue via phpmyadmin) (désolé pour le manque de lisibilité)

Edit : bon, difficile de rendre ca lisible vu la taille du nom de mes champs, donc j'ai eu recours a la capture d'ecran (par contre faut de bon yeux car j'ai réduit la taille)



Voila, j'espere que c'est plus lisible

J'ai remarqué que l'index etait ignoré seulement si id_country=1 (grand nombre de résultat), par contre pour toutes les autres valeurs l'index est utilisé. Est ce que MySQL verifie avant d'utiliser ou non un index si son utilisation est pertinente ?

[Adjanakis]

Il me semble que oui.

[yizashi]

Ce n'est pas parce que tu crées un index qu'il sera nécessairement utilisé. Le gestionnaire de base de données, en l'occurence MySQL décide si oui ou non il peut utiliser l'index puis si oui ou non il est avantageux de l'utiliser.

Si tes tables sont encore peu remplies alors il est fort probable qu'il décide que ça ne soit pas utile d'utiliser tes index.

Un index doit être créée en principe si le besoin s'en fait ressentir (mauvaises performances), si ta requête s'exécute rapidement ça ne sert à rien. En plus les index accélèrent les SELECT mais ralentissent les UPDATE et DELETE puisque le nombre de table à modifier est plus important.

Je crois qu'un index se justifie uniquement si ta table contient plusieurs milliers de ligne.

Il y a aussi des requêtes pour lesquelles l'utilisation d'index est impossible, notament celles dont la clause WHERE contient une fonction (style WHERE UPPER(nom) LIKE...), des NULL ou des NOT NULL. L'utilisation des fonctions sur la seconde partie du WHERE ne pose par contre pas de problème (WHERE nom LIKE UPPER(str)).

Personnellement je crois que country doit être une table relativement petite vu le nombre de pays que compte notre planète, donc c'est pas surprenant que l'index ne soit pas utilisé. Si je serais toi j'essaierais d'enlever le count, juste pour voir... malheureusement je ne connais pas bien les jointures MySQL mais je sais que selon le type de jointure que tu choisis tu as des performances nettement différente. Es-tu sûr que le simple "JOIN" est adapté?

[Matth_S]

Merci pour vos réponses !

En fait l'index sur country est tres important , si id_coutry!=1, le temps d'exectution passe de 5 sec a 0,5 sec , alors que sans l'index quelque soit l'id_country, le temps d'execution est de plus de 5 sec.
Ma table country est petite en effet (une centaine d'entrées), mais la il s'agit de l'id_country dans la table coord_recherche (clé étrangere donc) qui contient environ 100 000 entrées. L'index sur id_country me permet dans les autres cas d'eleminer tres rapidement les lignes qui ne correspondent pas a la recherche.

Je pense qu'Adjanakis a répondu a ma question, l'index n'est pas utilisé si ca n'est pas pertinent (dans le cas présent id_country=1 correspond a environ 80 % des données de la table coord_recherche) ; je ne savais pas que MySQL pouvais verifier avant d'executer une requete si oui ou non l'utilisation de l'index est nécessaire.

Ma question de pourquoi MySQL n'utilise pas l'index est donc résolu en revanche, je suis toujours preneur si vous avez une idée de comment optimiser cette requete dans le cas ou id_country=1.