Aller au contenu


Photo

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


  • Please log in to reply
7 réponses à ce sujet

#1 muqaddar

muqaddar

    Mouleur de chocolats

  • Maître Chocolatier
  • 10 888 messages

Posté 31 juillet 2013 - 13:50

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 ?


Mes applications iOS: VinoCell

#2 AliGator

AliGator

    Mouleur de chocolats

  • Contrôleur d'arômes
  • 13 621 messages
  • LocationRennes (France)

Posté 31 juillet 2013 - 14:23

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...ext-search.html
La Doc, c'est moi — Devise Shadok : S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !
CocoaPods : Getting StartedLe Blog CocoaPodsCherchez des podsDocs de pods
Mon GitHub: OHHTTPStubs, SwiftGen…
Mon Blog: Crunchy Development

#3 muqaddar

muqaddar

    Mouleur de chocolats

  • Maître Chocolatier
  • 10 888 messages

Posté 31 juillet 2013 - 15:08

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 CHARVARCHAR, 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 ?


Mes applications iOS: VinoCell

#4 AliGator

AliGator

    Mouleur de chocolats

  • Contrôleur d'arômes
  • 13 621 messages
  • LocationRennes (France)

Posté 31 juillet 2013 - 15:15

Non je ne crois pas tu peux y aller.
La Doc, c'est moi — Devise Shadok : S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !
CocoaPods : Getting StartedLe Blog CocoaPodsCherchez des podsDocs de pods
Mon GitHub: OHHTTPStubs, SwiftGen…
Mon Blog: Crunchy Development

#5 muqaddar

muqaddar

    Mouleur de chocolats

  • Maître Chocolatier
  • 10 888 messages

Posté 31 juillet 2013 - 22:01

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)


  • AliGator aime ceci
Mes applications iOS: VinoCell

#6 muqaddar

muqaddar

    Mouleur de chocolats

  • Maître Chocolatier
  • 10 888 messages

Posté 31 juillet 2013 - 22:24

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.


Mes applications iOS: VinoCell

#7 AliGator

AliGator

    Mouleur de chocolats

  • Contrôleur d'arômes
  • 13 621 messages
  • LocationRennes (France)

Posté 01 août 2013 - 00:45

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…
La Doc, c'est moi — Devise Shadok : S'il n'y a pas de solution, c'est qu'il n'y a pas de problème !
CocoaPods : Getting StartedLe Blog CocoaPodsCherchez des podsDocs de pods
Mon GitHub: OHHTTPStubs, SwiftGen…
Mon Blog: Crunchy Development

#8 muqaddar

muqaddar

    Mouleur de chocolats

  • Maître Chocolatier
  • 10 888 messages

Posté 01 août 2013 - 08:15

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.mysqltuto...-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.


Mes applications iOS: VinoCell




0 utilisateur(s) li(sen)t ce sujet

0 membre(s), 0 invité(s), 0 utilisateur(s) anonyme(s)