Question au sujet des indexes

Bonjour, apres plusieurs EXPLAIN sur mes requetes qui sont souvent de la meme forme, je vois quel n'utilisent pas les INDEX que j'ai mis. Je vais donc tacher de faire un exemple simple si quelqu'un pourrait m'expliquer ce qui est le mieux est pourquoi je lui dis d'avance un grand merci.

Table cpt_user

Code:

---

1 2	user_id, user_nick, user_pass, user_profile_stt

---

Table cpt_photos

Code:

---

1 2	user_id, photo_url, photo_first, photo_stt

---

Requete

Code:

---

1 2 3 4 5 6

SELECT b.photo_url FROM cpt_user AS a JOIN cpt_photos AS b ON a.user_id = b.user_id WHERE a.user_profile_stt = '1' AND b.photo_first='1' AND b.photo_stt='1' ORDER BY a.user_id DESC LIMIT 8

---

Comme tes colonnes user_profile_stt, photo_first et photo_stt semblent être des booléens :
- inutile d'entourer la valeur numérique de parenthèses ;
- inutile de poser un index dessus, il ne sera jamais utilisé car pas assez sélectif.

J'imagine que user_id est clé primaire dans la première table, donc indexée, et clé étrangère dans la seconde, donc elle doit être indexée.

Tu dis que tu fais des EXPLAIN mais si tu ne nous le donnes pas, on ne peut pas l'analyser !

Bien vus (pour les champs first et stt c'est du ENUM)

Le explain :

Code:

---

1 2 3 4 5 6 7 8

id select_type table type possible_keys key key_len ref rows Extra     1 SIMPLE b ALL user_photo_unik,search_photo_first,search_photo NULL NULL NULL 79 Using where; Using temporary; Using filesort     1 SIMPLE a eq_ref PRIMARY,search_relation_stt,search PRIMARY 4 dspaxpro.b.user_id 1 Using where

---

En sachant que (En voulant faire bien) :
sur la table user j'ai un index (search_relation_stt) : user_id,user_profile_stt
sur la table photo j'ai un index (search_photo_first) : user_id,photo_first,photo_stt

Citation:

---

pour les champs first et stt c'est du ENUM

---

Ça déjà, c'est pas terrible !
Il vaut mieux faire une table des "stt" (statuts ?) et faire une jointure dessus.
Le jour où tu veux ajouter un statut, il suffit d'ajouter une ligne dans la table au lieu de modifier la structure des tables qui utilisent ces statuts.

Ton explain n'a rien d'étonnant. Je suppose que tu as très peu de valeurs de "stt" dans ton ENUM donc l'index n'est jamais utilisé, surtout que tu as encore peu de photos dans la table.

OK, donc au niveau STT je suis OK je pourrai passer par une autre table sauf que le stt profile et différent du stt photo et que il ne sont pas lié au niveau du nombre de statut qui son définitif.

Mon problème ce résume plutôt au niveau suivant :

La table user sur une requête simple user_id user_profil_stt utilise bien l'index associé.
Idem pour la table photo.

Le truc c'est que ma requête fait juste la jointure au niveau du user_id et que du coup il n'utilise ni l'un ni l'autre. Après je suis OK avec toi, la table a peu de ligne actuellement car en cours de développement. Peut etre cela viens de la.

MA question est donc en essayant d’être plus claire.

Comment faire pour que Mysql utilise des index(et que mettre en index) pour qu'il les utilise en sachant que dans chacune des 2 tables jointes il y a le même user_id (qui est le JOIN ON) et que par table respective certaine valeurs doivent être entre guillemet définit c'est a dire tel champs avec tel valeurs.

Le schema est tjs le meme :

Table A et Table B lié par un user_id (ou autre, une relation quoi)

et que dans mon WHERE j'ai a.champs1 = Value , a.champs2 = Value, b.champs3 = Value

Pour tout savoir sur les idnex, va faire un tour chez SQLPro.

Les colonnes portant les clés étrangères doivent toujours être indexée et la jointure se fera en principe en utilisant ces index.

Pour les autres colonnes, qui peuvent faire l'objet d'une recherche de valeur dans le WHERE, il n'est utile d'indexer que celles contenant une forte proportion de valeurs différentes par rapport au nombre de lignes de la table.

Par exemple, si j'indexe le sexe sur une table de personnes, l'index aura environ 2 valeurs différentes et chaque valeur d'index retournera un fort pourcentage de lignes. L'optimiseur jugera que cet index n'est pas assez sélectif et que quitte à ramer 50% des lignes de la table, autant tout ramener et scanner toute la table.

Si par contre j'indexe le nom de la personne, il y a peu de chance qu'on trouve des milliers de "Leménager" ou de "Dembroski" donc lindex est pertinent et sera utilisé par le SGBD.

Sur le prénom, c'est déjà moins évident car des "Philippe", il peut y en avoir beaucoup.

En résumé, plus l'index est sélectif (retourne une petite proportion de lignes de la table), plus il aura de chances d'être utilisé.

Si tu as 5 statuts sur 10000 lignes, il y a peu de chance que l'index sur le statut soit utilisé.

Bah écoute merci pour cette réponse détaillé qui me rassure :) Sa veux donc dire qu'avec beaucoup de ligne dans tous les cas sa utilisera les index sur le user_id ce qui est amplement suffisant :)

Merci l'ami :)

Dernier détails, est ce que le JOIN ON a.user_id=b.user_id sur des tables a beaucoup de ligne utilisera les index user_id respectif ou il faut pour cela mettre dans le where a.user_id = b.user_id ???

La condition de jointure se met dans le ON. Inutile de la répéter dans le WHERE. Tu mélangerais la syntaxe moderne (JOIN ON) avec la vieille syntaxe obsolète depuis 20 ans (FROM des, tables WHERE conditions de jointure).

C'est bien mieux d'écrire ses jointures avec JOIN.

Citation:

---

Sa veux donc dire qu'avec beaucoup de ligne dans tous les cas sa utilisera les index sur le user_id ce qui est amplement suffisant

---

Pas forcément non, une jointure via index n'est pas forcément la jointure la plus rapide.

Du coup le SGBD peut choisir d'autre type de jointure selon .... beaucoup de critère.

Bref, il faudrai un cas bien précis pour pouvoir répondre à une question du type : est-ce normal que ma jointure se fasse sans l'utilisation d'un index ?

salut,

dans certain cas de jointures multiples, tu dois même parfois forcer les choses avec l'opérateur de jointure straight_join pour garantir un ordre de jointure... lire ça et la doc sur la syntaxe des jointures...

un ensemble d'exemples sur comment utiliser explain pour voir comment optimiser avec des index (en anglais)

et un diaporama:[ame="http://www.slideshare.net/techdude/how-to-kill-mysql-performance"]How to Kill Mysql Performance@@AMEPARAM@@ssplayer2.swf?doc=how-to-kill-mysql-performance-1201635569837936-3&stripped_title=how-to-kill-mysql-performance@@AMEPARAM@@how-to-kill-mysql-performance-1201635569837936-3@@AMEPARAM@@how-to-kill-mysql-performance[/ame] fait par un des concepteurs de mysql sur comment éviter de plomber son application mysql (en anglais)