optimiser une requete mysql

[chava2b]

Salut,

J'ai une requete qui est un peu longue et je voulais savoir si on peut l'optimiser:

Exemple avec le mot "PILE" [4 lettres]
Je veux recuperer tous les mots dans ma table qui ont entre 3 et 5 lettres, composés seulement des lettres P, I, L et E

Voici ce que je fais:

SELECT mot FROM ma_table WHERE mot RLIKE '^[pile]{3,5}$' AND mot LIKE '%p%' AND mot LIKE '%i%' AND mot LIKE '%l%' AND mot LIKE '%e%'


Pensez vous qu'on peut mieux faire?

Merci

 

[julienr]

salut,
connaissais pas RLIKE mais :

SELECT mot FROM ma_table WHERE mot RLIKE '^[pile]{3,5}$'

ne suffit pas ?

 

[chava2b]

julienr, le principe est de retrouver le mot PLIE (inversion de lettre) ou PILLE (lettre doublée) [bien que ce mot n'existe pas].

Je ne veux pas le mot POLE, ni EPEE, ce qui serait le ca si j'enleve la suite du RLIKE

 

[julienr]

c relou à chaque fois les regex faut tatonner, mais là

[pile]{3,5} veut normalement dire ce que tu veux c'est à dire seulement composé de p, i, l, e au minimum 3 et au maximum 5 fois dans la chaine

 

[RiPSO]

Je ne veux pas le mot POLE, ni EPEE, ce qui serait le ca si j'enleve la suite du RLIKE

tu risque pas de trouver pole vu qu'il n'y a pas de "o". Par contre epee c'est bon tu as dit que t'acceptais les répétitions de lettres, non?

Parcequ'a première vue c'est la regex de julien qu'il te faut (ou tout du moins le principe, je n'ai pas vérifié si elle fonctionnait)

 

[YoyoS]

SELECT mot FROM ma_table WHERE mot RLIKE '^[p|i|l|e]{3,5}$'

Et un truc du genre ?

 

[chava2b]

oups, le mot "POLE" ne ressort effectivement pas, ca c'est bon

Par contre, il me faut au moins une fois toutes les lettres. Je ne veux pas EPEE dans l'exemple.


Sinon, le RLIKE '^[p|i|l|e]{3,5}$' est egal au RLIKE '^[pile]{3,5}$'

 

[julienr]

et si ton mot de départ c'est "meet"

tu dois pouvoir accépter "meeter" ?

 

[chava2b]

Julienr, il y aurai une lettre de trop pour "meeter"

Pour meet, j'ai comme resultat: mete, mette, emet, met ...

Si je ne mets que le rlike, j'ai aussi comme resulats: mme, tete,... que je ne veux pas

 

[RiPSO]

j'ai enfin trouvé quelqu'un qui cherche a faire des fonctions aussi tordues que les miennes :lol:

je serais bien curieux de savoir a quoi cette fonction va te servir

 

[chava2b]

RiPSO, c'est pour un moteur de recherche; C'est pour suggerer d'autres mots dans le cas où tu doubles une lettre ou bien que t'en inverses une.

 

[julienr]

ca porte un nom çà et il me semble même que php propose une implémentation

 

[HawkEye]

oups, le mot "POLE" ne ressort effectivement pas, ca c'est bon

Par contre, il me faut au moins une fois toutes les lettres. Je ne veux pas EPEE dans l'exemple.


Sinon, le RLIKE '^[p|i|l|e]{3,5}$' est egal au RLIKE '^[pile]{3,5}$'

A mon avis tu vas avoir du mal à trouver des mots de 3 lettres qui contiennet au moins une fois les 4 lettres du mot de départ :roll:

 

[chava2b]

HawkEye, c'est vrai que pour un petit mot ce n'est pas parfois pas appreciable, ca fontionne mieux pour des mots un peu plus longs

 

[julienr]

ok mais bon il semble pas que ta requête du départ réponde à cette contrainte

SELECT mot FROM ma_table WHERE mot RLIKE '^[pile]{3,5}$' AND mot LIKE '%p%' AND mot LIKE '%i%' AND mot LIKE '%l%' AND mot LIKE '%e%'

 

[chava2b]

Oui la requete fait exactement ce que je veux, elle est seulement un peu lente...

 

[julienr]

c'est claire que like c loin d'être performant ...

peut être que

INSTR( mot, 'i') > 0

sera plus rapide.

 

[HawkEye]

HawkEye, c'est vrai que pour un petit mot ce n'est pas parfois pas appreciable, ca fontionne mieux pour des mots un peu plus longs

Ce que je veux dire, c'est que si tu exiges au moins une occurrence de chacune des 4 lettres, le mot ne peut pas faire 3 lettres de long
(mais ça ne répond pas à ta question, j'en conviens)

 

[chava2b]

julienr, c'est tres sensiblement pareil; 6 requetes avec le like et 6 avec instr: entre 3 et 5% de mieux pour le instr

C'est deja ca!!!!


HawkEye, tu as raison, il faudrait que je detecte s'il y a au moins 2 lettres identiques dans le mot. Si ce n'est pas le cas, je peux reduire ma recherche

Dans l'exemple : RLIKE '^[pile]{4,5}$'

 

[julienr]

ou sinon utiliser un index fulltext sur cette colonne ?

 

[chava2b]

Pourquoi pas, je vais essayer le fulltext dès que je trouve 5mn.

Merci

 

[julienr]

oui en faite non, si tu as 5min le plus performant reste de calculer les combinaisons possible en php (avec une fonction récursive) et d'effectuer une requête du genre :

SELECT mot FROM ma_table WHERE mot in ( 'PILE', 'PILLE', ..., 'PPLEI' );

avec un index sur la colonne mot çà devrait autrement plus dépoter qu'avec des like.

 

[RiPSO]

je pense aussi que c'est une bonne solution, mais j'ai un peu peur de ce que ça pourrait donner avec des mots de 7 ou 8 lettres... j'ai déjà testé les query avec des tableaux d'une cinquantaine de valeurs textes (url) et ça carburait pas vraiment, avec des mots de 8 caractères ça risque de faire pas mal de possibilités...

 

[julienr]

en faite çà dépend du type de ta colonne, d'expérience des listes d'environ 1000 éléments qui matchaient une colonne de type int, aucun soucis

 

[RiPSO]

le texte ça a toujours été violent :mrgreen:

Limite je me demande si il ne devrait pas faire un champ en + dans sa base avec la longueur des mots pour accelerer la chose.
Genre t'as une base "mots" avec les champs "id","mot","longueur" (longueur=nombre de caracteres du mot)

en prenant $txtlen = longueur du mot tapé par l'utilisateur.

dans ta query tu rajoutes "WHERE longueur>".($txtlen-1)." AND longueur<".($txtlen+1)

deja je pense que ca permettrait à mysql de limiter les lignes où il cherche...

 

[chava2b]

RiPSO, je viens de faire ton truc en ajoutant une colonne qui contient la longueur du mot: c'est deja beaucoup mieux.

je n'ai pa encore testé avec du fulltext;

Je peux me tromper mais pour cette histoire de permutation de lettre, le nbre de mots depend de la factorielle de la longueur du mot.
Un mot de 10 lettres me donne 3628800 autre mot possibles et il faut ajouter quelques mots de 9 et 11 lettres...

 

[RiPSO]

Ravi d'avoir pu aider

Par contre il me semble que ca ne fonctionnera plus avec du fulltext... Je peux peut être me tromper mais je sais qu'il y a des restrictions sur le fulltext pour que ca améliore les performances

 

[julienr]

nan effectivement fulltext n'est pas adapté dans ce cas de recherche sur juste un mot mais plutôt généralement sur du texte. Nan là typiquement ce type de problématique s'apparente à de la recherche phonétique, mais comme semble t-il ce n'est implémenté et optimale dans mysql que pour des expressions anglophone, il faudrait tout construire avec php qui implémente aussi ce type d'algorithme.
grosso mode il faudrait rajouter une colonne soundex qui contiendrait le soundex du mot en base puis ensuite simplement de faire une recherche sur cette colonne avec le soundex du mot à rechercher...