SQL: optimiser une recherche avec 2 "collate"

Dev
1

Salut les gens ! J’ai besoin de vos lumières.

Voici mon problème mySQL (qui est peut-être un problème SQL en général) :

  • J’ai une table d’association de mots (en gros, une table « word1 / word2 »); ces mots sont en français, en espagnol, en japonais, …
  • J’ai besoin de faire des recherches sensibles à la casse et aux accents sur le mot1
  • J’ai aussi besoin de faire une recherche qui me trouve tous les mots (de la première colonne) identiques à un mot donné à la casse/accent près.

Pour cela, pour l’instant, j’ai créé la table avec en collation : utf8_bin
J’ai créé un index sur la colonne word1.
Cela me permet de faire très rapidement la recherche « SELECT * from T_XXX where word1=‹ mot › »; (automatiquement, la recherche est case sensitive, accent sensitive). Cette recherche prend environ 0,05s.

En revanche, la recherche « SELECT Distinct word1 FROM T_XXX s where (s.word1=‹ mot › collate utf8_general_ci); » est très longue (de 2 à 8 secondes, notamment dans le cas où ‹ mot › ne serait pas dans la table).

J’ai pu optimiser la requête en « SELECT word1 FROM (SELECT Distinct word1 FROM T_XXX s) s2 where (s2.word1=‹ mot › collate utf8_general_ci); »
Cette requête ci va nettement plus vite (0,5 secondes), mais cela reste énorme comparé à la recherche sans conversion de caractères… et je ne parle pas de la tronche que me faire l’explain (450000 lignes à traiter… oups).

Manifestement, mySQL ne profite pas de l’index pour faire le « where… collate ».

L’une des solutions que je vois serait de créer une autre table listant tous les mots de word1, mais où cette table serait en collate utf8_general_ci.
Seulement je ne trouve pas cela très « beau », car pour cela, il faudrait penser à mettre cette deuxième table en concordance avec la première.
Les vues, elles, ne semblent d’aucune aide en terme de performance.

Existe-t’il un autre moyen d’optimiser la recherche en question ?

Merci :slight_smile:

2

la perte de performances s’explique par le fait qu’une collation étant la définition d’un ordre de tri dans un encodage donné, un index créé avec une collation n’est pas pertinent avec une autre.

tu n’as pas la possibilité de changer directement la collation de la table en question?

3

Le problème est que j’ai réellement besoin de faire à la fois des requêtes où les accents comptent, et des requêtes où les accents ne comptent pas.
De fait, si je change la collation de la table, ce ne sont plus les requêtes où les accents ne comptent pas qui vont ramer, mais celles où les accents comptent, et le problème sera le même…

4

Si tes collations peuvent être définies par colonne, tu as aussi la solution d’ajouter une colonne avec une collation différente. La taille de la table va doubler, mais tes index resteront rapides.

5

Oui mais existe-t’il un moyen de ne pas « répéter » l’information ?
Dans la solution que tu m’indiques, il faudrait que dans chaque « Insert », chaque « Update », ces 2 colonnes soient toujours identiques.

En fait, je me surtout une question de conception. Des solutions techniques « un peu rustine », effectivement, celle là est bien; comme celle d’avoir une table à part avec la liste de tous les mots. Mais existe-t’il un solution propre et automatique ? (J’aimerais éviter d’avoir un doublon pour rien, principalement).

6

En attendant qu’il y ait la possibilité d’affecter à une colonne plusieurs indexs avec des collations différentes, je pense qu’il n’y a rien de mieux que de dédoubler la colonne dans la table.