Bonjour,
 
Je fais appel à votre aide car j'ai un problème de temps d'exécution sur une requete impliquant une jointure sur une clé primaire.
Je travaille sur une base MySQL.
 
Voici ma base :
 
3 tables :
 
entreprise (id_entr, nom, adresse... )
motcle (id_mot, valeur)
entr_mot (num_entr, num_mot)
 
Il s'agit tout simplement de lier des entreprises avec des mots clés pour faire un petit moteur de recherche. Mon jeu de test comporte 100 000 entreprises, 50 000 mots clés et 1 700 000 tuples dans la table "entr_mot" liant les entreprises et les mots clés.
 
 
A cette structure très simple viennent s'ajouter quelques index :
 
table "entreprise" :
- une clé primaire sur id_entr
 
table "motcle" :
- une clé primaire sur id_mot
- un index fulltext sur valeur (utilisé pour les recherche de type MATCH... AGAINST...)
 
table "entr_mot" :
- un index sur num_entr
- un index sur num_mot
 
 
 
 
Voici la requête que je réalise sur cette base :
 
SELECT num_entr
FROM motscle
INNER JOIN entr_mots ON (id_motcle=num_motcle)
INNER JOIN entreprise ON (id_entr=num_entr)
WHERE MATCH(valeur) AGAINST('+voiture' IN BOOLEAN MODE)
OR MATCH(valeur) AGAINST('+rouge' IN BOOLEAN MODE)
GROUP BY num_entr
HAVING COUNT( num_entr ) = 2
LIMIT 0 , 30
 
C'est à dire que je recherche toutes les entreprises qui sont liées à la fois au mot voiture et au mot rouge.
 
Mon problème :
 
- Cette requête est excessivement lente avec la clé primaire id_mot (20 à 30 secondes).
 
- Si je supprime cette clé primaire ou que je la remplace par un index classique, alors la requête est quasiment instantanée (moins de 0.5 secondes).
 
- Si je ne réalise le test que sur un seul mot clé (donc en supprimant la ligne avec le OR) alors la requête est également instantanée même avec la clé primaire !
 
 
N'hésitez pas à me faire partager vos connaissances. Merci pour votre aide.

Je me demande si t'aurais pas intérêt à mettre un seul index dans ta table entr_mot qui serait composé des 2 clés étrangères...

t'as fait un explain de ta requête pour voir ce que fait mysql?

le double match against est inutile et pourrit tes perfs ( tu te frappes deux fois le parcours de ta table et par consequence de la table de liaison pour rien) et ton"+" ne sert également à rien vu qu'il n'y a qu'un seul mot.
Tu peux l'écrire ainsi:

match(valeur) against ('(rouge voiture)' IN BOOLEAN mode)

essaye déjà avec ça on verra ce que ça donne

 
J'avais fais l'essai mais les résultats étaient déplorables et c'est finalement assez logique car les deux jointures se basent sur les 2 index de manière distincte et non sur un index double.

match(valeur) against ('(rouge voiture)' IN BOOLEAN mode)

 
 
Je viens d'essayer ta proposition et ça me semble tout bon. Ce qui m'étonne c'est que j'avais pensé à cette solution et les résultats retournés ne correspondaient pas à ce que je voulais. Cependant à force de faire des tests, j'en perds le nord. Donc je n'avais peut-être pas fais exactement le même test. Je reposterai si je me rend compte qu'il y a un problème.
Est-ce que les parenthèses que tu as mis autour de rouge et voiture jouent un role particulier ? J'ai essayé avec et sans mais pas de différence. Est-ce pour prioriser des mots clés ?
 
En tout cas merci de ton aide

non effectivement les parenthèses ne servent strictement à rien et tu dois obtenir les mêmes resultats  
Elles ne sont utiles que pour définir des sous-expressions par exemple si tu cherches: voiture et ( bleu ou rouge) ça s'écrit:

mais là vu qu'il n'y a que ça dans la pattern, c'est totalement inutile.

Ah d'accord très bien.
merci beaucoup pour ton aide.