optimisation MySQL possible ? [Résolu]

optimisation MySQL possible ? [Résolu] - SQL/NoSQL - Programmation

Marsh Posté le 29-04-2009 à 00:00:36    

Bonjour à tous,
 
je sollicite votre aide pour un petit problème dont je ne trouve pas vraiment de solution.
J'ai un site avec une (plutôt) grosse base de données. J'ai une table membre qui ressemble à cela (id_membre, nom, prenom, telephone, adresse).
 
Cette table est plutôt conséquente (c'est relatif) puisqu'elle fait un peu plus de 100 Mo.
Sur mon site, j'ai un formulaire de recherche qui permet de rechercher dans cette table sur tous les champs. Il suffit de taper un morceau de nom, un morceau d'adresse...etc.
 
J'ai donc une requete du style:

Code :
  1. SELECT
  2.         nom,
  3.         prenom,
  4.         telephone,
  5.         adresse
  6. FROM
  7.         membre
  8. WHERE
  9.         nom LIKE '%$recherche%'
  10.         OR prenom LIKE '%$recherche%'
  11.         OR telephone LIKE '%$recherche%'
  12.         OR adresse LIKE '%$recherche%'
  13. LIMIT
  14.         0,100


 
En cherchant à optimiser cela, je suis tombé sur cet article http://www.vulgarisation-informati [...] -mysql.php. Article qui me va bien puisque je ne pourrais me prétendre expert en SGBD.
Résultat: j'ai l'impression ne rien pouvoir optimiser:
 
1) Eviter le SELECT *.
    C'est fait. Mais comme je récupère toute la ligne, je n'y gagne pas grand chose.
 
2) Fermer la connexion MySQL le plus tôt possible.
    C'est fait, mais là aussi je ne pense pas que ca va me débloquer.
 
3) Eviter la clause LIMIT
    Je ne peux pas faire autrement. Je ne restreint pas sur un champ numérique, donc je ne peux pas faire un  
    simple BETWEEN 0 AND 100. Est-ce que c'est possible de faire un équivalent de LIMIT autrement? en tout cas
    moi, je ne vois pas.
    Donc, pour le moment, c'est du SELECT de 100 Mo à chaque requete. Ouch >_<
 
4) Utilser des index
    D'après mes connaissances, j'ai cru comprendre qu'il n'était pas judicieux de mettre des index sur des champs texte. Et comme je ne cherche pas sur des champs numérique, et seulement sur des champs texte, je suis pénalisé. Mettre des index sur nom ou prenom n'est pas forcément la meilleure chose à faire, il me semble.
 
5) Mettre les table en mémoire
    Une table membre c'est quand même des infos importantes. De plus, mes compétences systèmes actuelles sont trop limitées pour gérer les sauvegardes de la base depuis la ram.
 
6) La clause EXPLAIN m'indique que ma requete fait partie des moins optimisées qui puissent être  :sweat:
 
 
Je suis hébergé sur un RPS1 de OVH. Les accès disque sont le principal point faible. Chaque requete m'est générée en entre 2 et 6 secondes environ, ce qui fait beaucoup. La seule solution que je vois actuellement est de passer à l'upgrade de serveur.
Voyez-vous d'autres solutions? Merci pour votre aide.
 
Cordialement.


Message édité par welcominh le 05-05-2009 à 21:32:14
Reply

Marsh Posté le 29-04-2009 à 00:00:36   

Reply

Marsh Posté le 29-04-2009 à 13:19:35    

Regarde du coté des full text search :  
http://dev.mysql.com/doc/refman/5. [...] earch.html

Reply

Marsh Posté le 29-04-2009 à 22:42:48    

Bonjour,
j'ai déjà testé cette méthode il y a quelques mois, même si je ne suis pas expert en full text search. Voici ce que j'en avais tiré:

Citation :


Il y a un inconvénient apparemment avec le match, c'est qu'il n'inclut apparemment pas le comportement du like '%...%'.
Ce qui veut dire que quand j'ai match(nom) against('chevalier'), j'obtiens seulement les lignes ou il y a le mot chevalier dans le nom, séparé par des espaces. Par exemple je n'obtiens pas les lignes dont le nom est "les_chevaliers_du_zodiaque".

 

C'est principalement pour cette raison que j'ai abandonné le MATCH, le principe du like '%...%' étant un critère obligatoire de ma recherche.
Si tu as d'autres piste, je suis bien preneur :)


Message édité par welcominh le 29-04-2009 à 22:51:34
Reply

Marsh Posté le 30-04-2009 à 07:14:39    

'*chevalier*'
 
faut lire la doc...

Reply

Marsh Posté le 02-05-2009 à 01:33:11    

Au risque de te contredire à tort, ca n'est pas possible.
La doc précise bien que le * ne peut se mettre qu'en suffixe. En cherchant sur internet, je ne vois également que des 'truc*' et non des '*truc*'.


Message édité par welcominh le 02-05-2009 à 01:34:20
Reply

Marsh Posté le 02-05-2009 à 16:22:22    

Mea culpa pour la mauvaise solution et pour ma remarque concernant la documentation.

Reply

Marsh Posté le 03-05-2009 à 21:26:04    

Ce n'est rien. Par contre j'encaisse mal ce genre de remarques (avec un ton "pffff", je l'ai ressenti comme ca), que ce soit à tort ou à raison.
Je ne t'en veux pas mais ca a plutôt tendance à me décourager qu'à progresser. Enfin, dans mon cas au moins. Je ne pourrais en faire une généralité.

 

Là où je te remercie, c'est que j'ai réétudié la solution des index full text et l'ai mis en oeuvre.
J'ai donc une requête du style

Code :
  1. WHERE
  2.     MATCH(nom, prenom, adresse, telephone) AGAINST('+mot1* +mot2*...+motn*' IN BOOLEAN MODE)


Ca ne récupère que les lignes contenant des mots commençant par mot1, mot2...etc. Cela implique forcément d'écrire au moins le début des mots. Pour le mot "dupon" par exemple, on aura donc les lignes contenant "dupond" ou "dupont" mais pas "chezdupond".
De plus, il faut que les mots dans les lignes soient séparés par des caractères séparateurs, tels que le tiret, le point, l'underscore...etc. Ces caractères peuvent être modifiés dans la config MySQL je crois. On aura tout de même donc les lignes "chez_dupond".

 

Après quelques tests, les résultats retournés, bien que pas aussi pertinents que le like '%...%' qui permet une correspondance stricte quelle soit la chaine autour du mot clé, sont tout de même assez satisfaisants. Le gain de performances est par contre incomparable. Là ou auparavant la requête prenait plusieurs secondes, là elle ne fait que quelque dixième de sec tout au plus. Le rapport pertinence / temps d'exécution penche alors largement pour cette dernière solution.

 

Donc pour le moment, à défaut de trouver encore mieux que cela, j'ai adopté les index full text :)
Merci en tout cas guybrush02.


Message édité par welcominh le 03-05-2009 à 21:28:18
Reply

Marsh Posté le 05-05-2009 à 15:14:12    

juste à propos du 4° point dans le premier poste : c'est faux, les champs textes tirent parfaitement partir des index.
 
par contre, effectivement un like '%toto%' n'utilisera pas d'index.

Reply

Marsh Posté le 05-05-2009 à 21:27:29    

hum oui en effet, je sais pas pourquoi je garde l'idée que les champs texte ne peuvent pas bénéficier des index.
Ptet parce que c'est moins "évident" qu'un champ numérique? enfin bon, merci de me rappeler à l'ordre :)

 

EDIT: et en effet, le like n'utilise pas les index. Un explain SQL me montre une estimation du nombre de lignes parcourues équivalent à la totalité de la table ^^'


Message édité par welcominh le 05-05-2009 à 21:29:42
Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed