›

Optimisation MySQL côté serveur pour une requête SELECT

muqaddarmuqaddar Administrateur
juillet 2013 modifié dans Langages Web & serveurs #1

Salut,


 


Je voudrais accélérer une requête serveur MYSQL toute bête:




SELECT * FROM table WHERE name LIKE '%search%'

Il y a 300.000 records dans la table, et actuellement une clé primaire 'id'.


 


La requête est affichée à  2,5s dans phpMyAdmin en moyenne.


 


Plus bizarrement, elle est à  3 ou 6s avec Active Record (Rails) ??? Sauf quand le cache se met en branle, on tombe à  9ms... mais ça ne sera pas souvent le cas vu les besoins ici, la recherche changeant à  chaque fois.


 


La base est sur un serveur mutualisé mais je ne crois pas que ça soit la cause des lenteurs ici.


 


 




Started GET "/services/search?search=jacobs%20creek" at Wed Jul 31 12:46:18 +0000 2013

  Processing by ServicesController#search as JSON

  Parameters: {"search"=>"jacobs creek"}

Completed 200 OK in 3913ms (Views: 12.2ms | ActiveRecord: 3836.7ms)



 


Question 1: faut-il mettre un index sur le champ 'name' de la table pour accélérer ce type de traitement ?

Réponses

  • AliGatorAliGator Membre, Modérateur
    juillet 2013 modifié #2
    Crée un index de type FULLTEXT sur ta colonne name, et utilise MATCH()...AGAINST() pour faire ta recherche, ça devrait grave améliorer les perfs (c'est fait pour).

    http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html
  • muqaddarmuqaddar Administrateur
    #3

    Ma table est en InnoBD...


     


    Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDBtables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.

     



     


    J'ai toujours utilisé de l'InnoDB avec Rails (mais je ne sais plus pour quelle raison à  l'époque).


    Il faudrait donc que je la convertisse. Je risque quelque chose ?

  • AliGatorAliGator Membre, Modérateur
    #4
    Non je ne crois pas tu peux y aller.
  • muqaddarmuqaddar Administrateur
    #5

    Bon, pour l'instant, j'ai juste passé la table en MYIsam et j'ai déjà  fait un x5 !


    ça tourne autour de 0.6s la requête.


    (et toujours beaucoup plus par Active Record par contre, mais dans le même ratio /5)


  • muqaddarmuqaddar Administrateur
    juillet 2013 modifié #6

    Deuxième test.


    J'ai créé mon index FULLTEXT.


    Je demande la requête via phpMyAdmin (comme avant).


    Je test avec MATCH et AGAINST.


     


    Je fais encore un x10 !!! Je suis tombé à  0.05s !


     


    Problème: la recherche est "trop intelligente" en pertinence: si je veux rechercher un équivalent de '%baguette épis%', il me placera en premier 'baguette épis baguette' si il y a 2 fois le mot baguette dans la base.


    Or, moi, justement, je ne veux pas les noms à  rallonge mais ceux qui se rapprochent le plus de la chaà®ne recherchée en prenant juste en compte les erreurs éventuelles de début et fin de chaà®ne, comme avec la clause LIKE avec le caractère '%'.


     


    EDIT: en utilisant find_by_sql de ActiveRecord et la clause MATCH, j'ai gagné beaucoup de temps également: je suis à  300 ms environ (contre 5s au début du sujet).


    Reste le problème de pertinence à  régler.


  • AliGatorAliGator Membre, Modérateur
    #7
    T'as testé avec "WITH QUERY EXTENSION" ?

    Je suis pas sûr de bien comprendre l'effet de ce modifier mais ça vaut le coup de tenter...
  • muqaddarmuqaddar Administrateur
    #8


    T'as testé avec "WITH QUERY EXTENSION" ?

    Je suis pas sûr de bien comprendre l'effet de ce modifier mais ça vaut le coup de tenter...




     


    Je ne connaissais pas. Il s'agit de WITH QUERY EXPANSION. ;)


    Je viens de tester, d'après ce que j'ai lu ça aurait dû avoir une incidence mais ça ne change pas grand chose en fait.


     


    http://www.mysqltutorial.org/using-mysql-query-expansion.aspx


     


    En effet, ça devrait donner les résultats les plus proches en premier... puis ensuite il élargit la recherche.


     


    Dans mon cas, si je recherche : "Chateau Talbot", il me renvoie quand même dans les premiers résultats : "Chateau Talbot Le Connetable de Talbot" parce qu'il va trouver 2 fois le mot Talbot.

Connectez-vous ou Inscrivez-vous pour répondre.