Discussion :

[bdgdgdfg]

Bonsoir,

J'avais demandé de l'aide sur ce site il y a déjà quelques années, avec de très bons retours et une bonne expérience. Je retente donc ma chance

Imaginez une table herbs contenant environ 300.000 enregistrements et ne seconde table herb_translations, contenant environ 1.000.000 enregistrements :



Désormais pour les requêtes...

Sélectionner les 20 premières herbes, triées par le nom de manière alphabétique, contenant une traduction FR :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select herbs.id, sciname, name 
from herbs
join herb_translations on herbs.id = herb_translations.herb_id and locale = 'fr'
order by herb_translations.name asc limit 20;

Aucun problème, cela passe à 100% via les INDEX.

Le problème : sélectionner les 20 premières herbes, triées par le nom de manière alphabétique, contenant une traduction FR, ET SI NON, renverra null pour le nom :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select herbs.id, sciname, name 
from herbs
left join herb_translations on herbs.id = herb_translations.herb_id and locale = 'fr'
order by herb_translations.name asc limit 20;

Ici cela ne fonctionne plus, c'est beaucoup beaucoup trop long, car il passe par filesort et non plus les index. Ce qui est normal sur papier mais pose problème, car je dois bien souvent afficher par ordre alphabétique dans une langue (et avoir la plante même s'il n'y a pas de traduction dans la langue demandée).

Quelqu'un aurait une idée, une parade, quelque chose ?

Merci d'avance pour vos avis éclairés,
Belle fin de journée à tous.

[escartefigue]

Ici, vu qu'on fait une jointure sur la PK des deux tables, pas de problème de performance sur ce point.

Mais, la différence entre les deux jointures c'est qu'avec la jointure externe, s'il y a une forte proportion de lignes sans correspondance, alors le tri concerne un très grand nombre de lignes qui ne sont pas ramassées par la jointure interne.
En effet, la clause LIMIT s'applique après la clause ORDER BY.

Quelle est la proportion de lignes de la table "herbs" ayant une correspondance dans la table "herb_translations" ?
Plus elle est forte, plus les perfs seront mauvaises.

Pour améliorer les performances dans ce cas, il faudrait indexer la colonne "name"

Par ailleurs, il est anormal d'avoir autant de colonnes "nullables" dans vos tables, les colonnes "nullables" devraient être l'exception.
Dans votre cas, toutes les colonnes de la table "herb_translations" à l'exception de sa PK sont nullables, ça signifie qu'on peut enregistrer une ligne ayant seulement un identifiant, rien d'autre...

[bdgdgdfg]

Merci pour votre réponse.

Elles ont toutes au moins une correspondance (la langue FR). Néanmoins, lorsque la locale du site est mise sur EN (par un membre), cela doit bien évidemment retourner la langue EN (et dans mon cas précis, retourner toutes les herbes, même celles qui ne sont pas EN, et dans l'ordre alphabétique - notamment pour l'administration).

La colonne "name" est bien indexée et cela fonctionne parfaitement avec un inner join, comme vous le dites, lorsque je trie alphabétiquement. Le problème étant le left join, et aucune solution visiblement, malgré pas mal de recherches.

Concernant la dernière remarque "nullables", il est vrai que cela doit changer, mais ce sont les champs de la colonne herbs qui sont nullables et non pas ceux des traductions.

Belle fin de journée

[escartefigue]

Elles ont toutes une correspondance en "FR", mais si ce n'est pas le cas en "EN" ça explique la forte augmentation de volume à trier entre la jointure INNER et la jointure OUTER, d'où l'impact sur les performances.
C'est très probablement la cause de la dégradation.

Une remarque : les "champs" sont des zones de formulaire ou d'états, dans une table d'une BDD relationnelle, il y a des "colonnes"

[Séb.]

Ici cela ne fonctionne plus, c'est beaucoup beaucoup trop long

Tu passes de combien à combien ?
Je suis prêt à tester sur différentes configs MySQL / MariaDB si tu mets à dispo la data.

[bdgdgdfg]

Elles ont toutes une correspondance en "FR", mais si ce n'est pas le cas en "EN" ça explique la forte augmentation de volume à trier entre la jointure INNER et la jointure OUTER, d'où l'impact sur les performances.
C'est très probablement la cause de la dégradation.

Une remarque : les "champs" sont des zones de formulaire ou d'états, dans une table d'une BDD relationnelle, il y a des "colonnes"

Merci pour votre réponse.

En effet, je me suis trompé, j'ai indiqué "ce sont les champs de la colonne", ce qui n'avait aucun sens. Merci pour la mise au point.

Du coup je viens d'effectuer un nouveau test avec 100.000 herbes et 400.000 traductions (en 4 langues différentes et donc TOUTES les herbes possèdent TOUTES les traductions).

Voici les résultats via les 2 requêtes suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select herbs.id, sciname, locale, name 
from herbs
join herb_translations on herbs.id = herb_translations.herb_id and locale = 'en'
order by name asc limit 20;

Requête effectuée en 0.015s : "using index condition" --> tout est ok.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select herbs.id, sciname, locale, name 
from herbs
left join herb_translations on herbs.id = herb_translations.herb_id and locale = 'en'
order by name asc limit 20;

Requête effectuée en 1.125s : "Using index; Using temporary; Using filesort" --> performance mauvaise (et encore ici, nous n'avons "que" 400.000 traductions).

Autrement dit, je ne trouve aucune solution efficace pour afficher les X herbes triées de manières alphabétique (et même si elle ne fait pas partie de la langue courante).

Merci encore pour votre réponse.

[bdgdgdfg]

Tu passes de combien à combien ?
Je suis prêt à tester sur différentes configs MySQL / MariaDB si tu mets à dispo la data.

Merci pour votre réponse.

Je passe de 0.015s à 1.125s, et ce, même si elles ont toutes une traduction dans la langue filtrée.

(et encore, ici je parle d'un test sur "seulement" 400.000 traductions)

Pas de problème pour fournir les données si vous désirez tester et que cela ne vous dérange pas, ce sont quand même des données insérées via un seeder.

[escartefigue]

Il faudrait également communiquer le script DDL complet de création des deux tables et de tous leurs index

[bdgdgdfg]

(il ne faut pas faire attention à la pièce jointe, je n'ai pas su la supprimer hum)

Merci pour votre réponse.

Voici le schéma tel qu'il est actuellement :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE `herbs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sciname` varchar(255) DEFAULT NULL,
  `user_id` bigint(20) unsigned,
  `created_at` timestamp NULL,
  `updated_at` timestamp NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `herbs_sciname_unique` (`sciname`),
  CONSTRAINT `herbs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
 
CREATE TABLE `herb_translations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `locale` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `herb_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `herb_translations_herb_id_locale_unique` (`herb_id`,`locale`),
  INDEX `herb_translations_locale_name_index` (`locale`,`name`),
  INDEX `herb_translations_locale_index` (`locale`),
  INDEX `herb_translations_name_index` (`name`),
  CONSTRAINT `herb_translations_herb_id_foreign` FOREIGN KEY (`herb_id`) REFERENCES `herbs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Bon, il y a clairement trop d'index sur herb_translations pour le moment mais j'avais effectué plusieurs tests. La solution actuelle n'est clairement pas la plus optimale mais je montre l'actuelle.

Belle journée, et merci.

[skuatamad]

Peut on voir les plans d'exécution ?

Et qu'est ce que ça donne en intégrant "name" dans l'index unique :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

UNIQUE KEY `herb_translations_herb_id_locale_name_unique` (`herb_id`,`locale`,`name`)

[bdgdgdfg]

Merci pour la réponse.

Voici les plans détaillés :

Tout d'abord pour le join normal (requête sans problème).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select herbs.id, sciname, locale, name 
from herbs
join herb_translations on herbs.id = herb_translations.herb_id and locale = 'en'
order by name asc limit 20;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
{
   "query_block": {
     "select_id": 1,
     "cost": 245.3794609,
     "nested_loop": [
       {
         "table": {
           "table_name": "herb_translations",
           "access_type": "range",
           "possible_keys": [
             "herb_translations_herb_id_locale_unique",
             "herb_translations_locale_name_index",
             "herb_translations_locale_index"
           ],
           "key": "herb_translations_locale_name_index",
           "key_length": "1022",
           "used_key_parts": ["locale"],
           "loops": 1,
           "rows": 199722,
           "cost": 66.3985088,
           "filtered": 100,
           "index_condition": "herb_translations.locale = 'en'"
         }
       },
       {
         "table": {
           "table_name": "herbs",
           "access_type": "eq_ref",
           "possible_keys": ["PRIMARY"],
           "key": "PRIMARY",
           "key_length": "8",
           "used_key_parts": ["id"],
           "ref": ["test_last.herb_translations.herb_id"],
           "loops": 199722,
           "rows": 1,
           "cost": 178.9809521,
           "filtered": 100
         }
       }
     ]
   }
 }

En ce qui concerne le left join :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
select herbs.id, sciname, locale, name 
from herbs
left join herb_translations on herbs.id = herb_translations.herb_id and locale = 'en'
order by name asc limit 20;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
{
  "query_block": {
    "select_id": 1,
    "cost": 290.0171456,
    "const_condition": "1",
    "filesort": {
      "sort_key": "herb_translations.`name`",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "herbs",
              "access_type": "index",
              "key": "herbs_sciname_unique",
              "key_length": "1023",
              "used_key_parts": ["sciname"],
              "loops": 1,
              "rows": 99955,
              "cost": 21.83689789,
              "filtered": 100,
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "herb_translations",
              "access_type": "eq_ref",
              "possible_keys": [
                "herb_translations_herb_id_locale_unique",
                "herb_translations_locale_name_index",
                "herb_translations_locale_index"
              ],
              "key": "herb_translations_herb_id_locale_unique",
              "key_length": "1030",
              "used_key_parts": ["herb_id", "locale"],
              "ref": ["test_last.herbs.id", "const"],
              "loops": 99955,
              "rows": 1,
              "cost": 200.9702342,
              "filtered": 100,
              "attached_condition": "trigcond(herb_translations.locale = ''en'')"
            }
          }
        ]
      }
    }
  }
}

Lorsque j'ajoute votre proposition :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
ALTER TABLE herb_translations
ADD UNIQUE KEY `herb_translations_herb_id_locale_name_unique` (`herb_id`, `locale`, `name`);

Le simple join prend désormais environ 1s (par rapport à l'instantané de la version précédente) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
{
  "query_block": {
    "select_id": 1,
    "cost": 241.1005746,
    "filesort": {
      "sort_key": "herb_translations.`name`",
      "temporary_table": {
        "nested_loop": [
          {
            "table": {
              "table_name": "herbs",
              "access_type": "index",
              "possible_keys": ["PRIMARY"],
              "key": "herbs_sciname_unique",
              "key_length": "1023",
              "used_key_parts": ["sciname"],
              "loops": 1,
              "rows": 99955,
              "cost": 21.83689789,
              "filtered": 100,
              "using_index": true
            }
          },
          {
            "table": {
              "table_name": "herb_translations",
              "access_type": "ref",
              "possible_keys": [
                "herb_translations_herb_id_locale_unique",
                "herb_translations_herb_id_locale_name_unique",
                "herb_translations_locale_name_index",
                "herb_translations_locale_index"
              ],
              "key": "herb_translations_herb_id_locale_name_unique",
              "key_length": "1030",
              "used_key_parts": ["herb_id", "locale"],
              "ref": ["test_last.herbs.id", "const"],
              "loops": 99955,
              "rows": 1,
              "cost": 152.0536632,
              "filtered": 100,
              "attached_condition": "herb_translations.locale = ''en''",
              "using_index": true
            }
          }
        ]
      }
    }
  }
}

Encore merci.

[escartefigue]

Je suppose que le code langue fait toujours 2 ou 3 caractères, aussi, choisir du varchar(255) est contre-performant, du char fixe char(2) ou char(3) serait nettement mieux, mais en réalité, c'est l'identifiant de la langue et non son code qui devrait être présent dans la table de traduction.
En effet, la traduction, au niveau conceptuel, est une association entre l'entité-type [HERBE] et l'entité-type [LANGUE]. C'est donc une table associative qui doit hériter des identifiants des entité-type participant à l'association (ce qui permettra aussi d'avoir les mêmes noms pour un même identifiant dans toutes les tables, ce qui est quand même bien plus pratique que "id" d'un coté et "id_herbe" de l'autre)
On devrait donc avoir pour identifiant le couple (id_herbe, id_langue), ce qui permettrait d'avoir un index cluster sur la table de traduiction trié de la même façon que l'index de la table herbe et ainsi optimiser les performances.

Il y a en effet des index inutiles sur la table de traduction, puisque le deuxième index (colonne "locale" seule) est inclus dans le 1er (colonnes "locale" + "name")

[EDIT] créer un index sur herbid + locale + name n'est vraiment pas idéal : deux colonnes varchar (255) soit 500 octets + l'identifiant, c'est très encombrant et attention aux désorganisations des pages data et index si la longueur effective change lors d'une mise à jour.
Essayez de modéliser la table associative comme il se doit, et cet artifice ne devrait plus être nécessaire.

[SQLpro]

Pour info, j'ai reproduit ton exemple avec Microsoft SQL Server...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
CREATE DATABASE DB_TEST;
GO
 
USE DB_TEST;
GO
 
CREATE TABLE herbs (
  id int NOT NULL IDENTITY,
  sciname varchar(255) DEFAULT NULL,
  user_id int,
  created_at datetime2(0) NULL,
  updated_at datetime2(0) NULL,
  PRIMARY KEY (id),
  UNIQUE (sciname)
  );
 
CREATE TABLE herb_translations (
  id int NOT NULL IDENTITY,
  locale varchar(255) NOT NULL,
  name varchar(255) NOT NULL,
  herb_id int NOT NULL REFERENCES herbs (id) ON DELETE CASCADE,
  PRIMARY KEY (id),
  UNIQUE (herb_id, locale)
);
GO

Simulation de charge avec des valeurs aléatoires :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
INSERT INTO herbs VALUES (NEWID(), 0, GETDATE(), NULL);
GO 23
 
INSERT INTO herbs 
SELECT NEWID(), 1, GETDATE(), NULL FROM herbs AS H1 CROSS JOIN herbs;
 
INSERT INTO herbs 
SELECT NEWID(), 2, GETDATE(), NULL FROM herbs AS H1 CROSS JOIN herbs;
 
INSERT INTO herb_translations VALUES (CONCAT(NEWID(), NEWID(), NEWID()), NEWID(), RAND() * 300000) ;
GO 32
 
INSERT INTO herb_translations
SELECT CONCAT(NEWID(), NEWID(), NEWID()), NEWID(), 1 + CAST((ABS(CHECKSUM(NEWID())) / 4345678901.0) * 300000 AS INT)
FROM herb_translations AS HT1
     CROSS JOIN herb_translations;
 
	 INSERT INTO herb_translations
SELECT CONCAT(NEWID(), NEWID(), NEWID()), NEWID(), 1 + CAST((ABS(CHECKSUM(NEWID())) / 4345678901.0) * 300000 AS INT)
FROM herb_translations AS HT1
     CROSS JOIN herb_translations;
 
UPDATE herb_translations
SET locale = 'fr'
WHERE herb_id % 3 < 1 ;

Soit : 1 116 192 lignes dans translation et 305 256 dans herbs...

Résultat sans index (sur un portable de m... avec un CPU 2 coeurs hyperthreadé (4 coeurs logiques) i7 à 2,5 Ghz:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
--> sans index :
select herbs.id, sciname, name 
from herbs
join herb_translations on herbs.id = herb_translations.herb_id and locale = 'fr'
order by herb_translations.name asc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
--> Temps UC = 423*ms, temps écoulé = 178*ms.
 
select herbs.id, sciname, name 
from herbs
left join herb_translations on herbs.id = herb_translations.herb_id and locale = 'fr'
order by herb_translations.name asc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
--> Temps UC = 498*ms, temps écoulé = 210*ms.

Avec les index suivants :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE INDEX X001 ON [dbo].[herb_translations] ([locale], [herb_id]) INCLUDE ([name]);
CREATE INDEX X002 ON [dbo].[herbs] (id) INCLUDE ([sciname]); 
 
select herbs.id, sciname, name 
from herbs
join herb_translations on herbs.id = herb_translations.herb_id and locale = 'fr'
order by herb_translations.name asc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
--> Temps UC = 250*ms, temps écoulé = 107*ms
 
select herbs.id, sciname, name 
from herbs
left join herb_translations on herbs.id = herb_translations.herb_id and locale = 'fr'
order by herb_translations.name asc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY;
--> Temps UC = 423*ms, temps écoulé = 140*ms.

Bref moins de 210 ms pour la pire des requête et 107 pour la meilleure

Quand je dis que MySQmerde est une daube...

A +

[bdgdgdfg]

Quand je dis que MySQmerde est une daube...

Merci pour votre réponse et vos tests.

Ha oui, la différence est flagrante... Probablement pas de solution efficace avec MySQL alors lorsqu'il commence à y avoir "pas mal" d'informations.

[bdgdgdfg]

Je suppose que le code langue fait toujours 2 ou 3 caractères, aussi, choisir du varchar(255) est contre-performant, du char fixe char(2) ou char(3) serait nettement mieux, mais en réalité, c'est l'identifiant de la langue et non son code qui devrait être présent dans la table de traduction.

Merci pour votre message.

J'ai effectué des tests avec les changements indiqués, toujours la même performance malheureusement.

Visiblement MySQL a du mal à trier les données de manière alphabétique lors d'un left join.

[escartefigue]

De mon côté, j'ai fait un essai avec un échantillon réduit : 25 noms scientifiques et 70 noms communs répartis dans 5 langues.
Je sais bien que les cardinalités peuvent grandement influer sur la stratégie de l'optimiseur, mais ici on voit une stratégie différente entre MySQL et MariaDB à cardinalités égales.

Tout d'abord, j'ai modélisé la base comme préconisé, à savoir une table des noms scientifiques, une table des noms communs et une table des langues.
La table des noms communs n'a pas d'identifiant propre, elle hérite de l'identifiant du nom scientifique et de l'identifiant de langue pour former sa PK.
J'ai également supprimé les index pléthoriques, mais ce n'est pas ce qui est en cause puisqu'ils n'étaient de toutes façons pas utilisés.
Et je n'ai pas ajouté d'index couvrant sur le nom commun, les seuls index sont ceux des PK respectives, plus celui de la contrainte unique sur le nom scientifique.

J'ai effectué les tests sous DB<>Fiddle

Avec MySQL V8.0 :
La jointure interne et la jointure externe n'utilisent pas le même chemin d'accès, mais la même stratégie (index/temp/filesort) , voici le résultat de l'explain (1 : inner join, 2 : outer join) :



et avec l'option ANALYZE :



Curieusement, sous MySQL, à chaque run, j'obtiens même une durée plus courte avec la jointure externe qu'avec la jointure interne.

Avec MariaDB V10.9 :
Les stratégies sont différentes, en défaveur de la jointure externe, voici ce qu'il en est (1 : inner join, 2 : outer join) :



Avec MariaDB, l'ajout d'un index TR_IX02 sur (HE_ident, LG_ident, TR_lib) ou sur (HE_ident, TR_lib) ou encore sur (TR_lib) seul ne change rien !
On a donc bel et bien un comportement different de MariaDB avec la jointure externe


À noter : pour des raisons historiques, dans la vraie vie, les noms scientifiques (latins) d'une même espèce animale comme végétale sont multiples. Il faudrait donc mettre en place une relation réflexive sur la table "herb", avec une notion de rang. Le rang 1 étant le nom officiel.

Le script complet :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
-- https://www.developpez.net/forums/d2163193/bases-donnees/mysql/requetes/mariadb-11-3-2-left-join-order-by-tres-lent-use-filesort/#post12009843
-- noms multilingues voir https://www.tela-botanica.org/bdtfx-nn-39692-ethnobotanique
CREATE TABLE HE_herb
     (  HE_ident  bigint unsigned NOT NULL AUTO_INCREMENT
      , HE_lib    varchar(255) NOT NULL
      , HE_cre    timestamp NOT NULL
      , HE_maj    timestamp NOT NULL
      , PRIMARY KEY (HE_ident)
      , UNIQUE  KEY HE_UK01 (HE_lib)
     ) 
; 
insert into HE_herb
       (HE_lib, HE_cre, HE_maj)
values ('Poa annua', current_timestamp(), current_timestamp()) 
     , ('Cynodon dactylon', current_timestamp(), current_timestamp()) 
     , ('Lolium perenne', current_timestamp(), current_timestamp()) 
     , ('Bellardiochloa variegata', current_timestamp(), current_timestamp()) 
     , ('Arrhenatherum elatius',  current_timestamp(), current_timestamp()) 
     , ('Hordeum bulbosum', current_timestamp(), current_timestamp()) 
     , ('Festuca cyrnea', current_timestamp(), current_timestamp()) 
     , ('Lolium rigidum lepturoides', current_timestamp(), current_timestamp())  
     , ('Pucinellia capillaris', current_timestamp(), current_timestamp())  
     , ('Puccinellia distans', current_timestamp(), current_timestamp())     
     , ('Puccinellia fasciculata', current_timestamp(), current_timestamp())    
     , ('Bromopsis benekenii', current_timestamp(), current_timestamp())    
     , ('Anisantha diandra', current_timestamp(), current_timestamp())    
     , ('Bromopsis pannonica', current_timestamp(), current_timestamp())   
     , ('Bromus hordeaceus jansenii', current_timestamp(), current_timestamp())   
     , ('Bromus japonicus subsquarrosus', current_timestamp(), current_timestamp())   
     , ('Bromus pseudosecalinus', current_timestamp(), current_timestamp())
     , ('Bromus pseudothominei', current_timestamp(), current_timestamp())
     , ('Bromus × gusuleacii', current_timestamp(), current_timestamp())
     , ('Bromus × hannoverianus', current_timestamp(), current_timestamp())
     , ('Bromus × karlobagensis', current_timestamp(), current_timestamp())
     , ('Bromus × laagei', current_timestamp(), current_timestamp())
     , ('Anisantha fasciculata', current_timestamp(), current_timestamp())  
     , ('Anisantha madritensis', current_timestamp(), current_timestamp())  
     , ('Anisantha rigida', current_timestamp(), current_timestamp())    
;  
select * from HE_herb
;  
CREATE TABLE LG_lang
     (  LG_ident smallint NOT NULL AUTO_INCREMENT
      , LG_code  char(2)  NOT NULL
      , LG_lib   varchar(30) NOT NULL
      , PRIMARY KEY (LG_ident)
     )
;
insert into LG_lang
       (LG_code, LG_lib)
values ('DE', 'Deutsch')
     , ('EN', 'English')
     , ('ES', 'Español')
     , ('FR', 'Français')
     , ('IT', 'Italiano')
;
select * from LG_lang
;
CREATE TABLE TR_traduc
     (  HE_ident  bigint   unsigned NOT NULL
      , LG_ident  smallint NOT NULL
      , TR_lib    varchar(255) NOT NULL
      , PRIMARY KEY (HE_ident, LG_ident)
--      , INDEX TR_IX02 (HE_ident, LG_ident, TR_lib)
      , CONSTRAINT TR_FK01
        FOREIGN KEY (HE_ident) 
        REFERENCES HE_herb(HE_ident) 
        ON DELETE CASCADE
      , CONSTRAINT TR_FK02
        FOREIGN KEY (LG_ident) 
        REFERENCES LG_lang(LG_ident) 
        ON DELETE RESTRICT
     ) 
;
insert into TR_traduc(HE_ident, LG_ident, TR_lib)
values (01, 1, 'Einjähriges Rispengras')
     , (01, 2, 'Annual Bluegrass')
     , (01, 3, 'Espiguilla anual')
     , (01, 4, 'Pâturin annuel')
     , (01, 05, 'Fienarola annuale')  
     , (02, 1, 'Hundszahngras')
     , (02, 2, 'Bermuda Grass')
     , (02, 3, 'Grama común')
     , (02, 4, 'Chiendent pied de poule')
     , (02, 05, 'Gramegna')  
     , (03, 1, 'Ausdauerndes Weidelgras')
     , (03, 2, 'Common Ray-grass')
     , (03, 3, 'Raigrás')
     , (03, 4, 'Ray-grass anglais')
     , (04, 1, 'Violettes Rispengras')
     , (04, 2, 'Violet Meadowgrass')
     , (04, 3, 'Poa violácea')
     , (04, 4, 'Faux-Paturin violacé')
     , (05, 1, 'Französiches Raigras')
     , (05, 2, 'False Oat-grass')
     , (05, 3, 'Avena descollada')
     , (05, 4, 'Fromental')
     , (05, 5, 'Avena maggiore')
     , (06, 2, 'Bulbous Barley')
     , (06, 4, 'Orge bulbeuse')
     , (06, 5, 'Orzo bulboso')
     , (07, 4, 'Fétuque')
     , (07, 5, 'Festuca di Corsica')
     , (08, 4, 'Ivraie')
     , (08, 5, 'Loglio marittimo')
     , (10, 1, 'Abstehender Salzschwaden')
     , (10, 2, 'European Alkaligrass')
     , (10, 4, 'Glycérie à épillets espacés')
     , (10, 5, 'Gramignone delle argille')
     , (11, 1, 'Büschel-Salzschwaden')
     , (11, 2, 'Borrer''s Saltmarsh-grass')
     , (11, 4, 'Glycérie de Borrer')
     , (11, 5, 'Gramignone delle bonifiche')  
     , (12, 1, 'Benekens Trespe')
     , (12, 2, 'Lesser Hairy Brome')
     , (12, 4, 'Brome de Beneken')
     , (12, 5, 'Forasacco di Beneken')
     , (13, 1, 'Gussones Trespe')
     , (13, 2, 'Rip-gut Brome')
     , (13, 3, 'Bromo')
     , (13, 4, 'Brome à deux étamines')
     , (13, 5, 'Forasacco di Gussone')
     , (14, 4, 'Brome')
     , (15, 4, 'Brome')  
     , (16, 4, 'Brome')
     , (17, 2, 'Smith''s Brome')
     , (17, 4, 'Brome')
     , (18, 2, 'Lesser Soft-brome')
     , (18, 4, 'Brome')
     , (19, 4, 'Brome')
     , (20, 4, 'Brome')
     , (21, 4, 'Brome')  
     , (22, 4, 'Brome') 
     , (23, 4, 'Brome fasciculé')
     , (23, 5, 'Forasacco insulare')
     , (24, 1, 'Madrider Trespe')
     , (24, 2, 'Compact Brome')
     , (24, 3, 'Bromo madritense')
     , (24, 4, 'Brome de Madrid')
     , (24, 5, 'Forasacco dei muri')
     , (25, 1, 'Rauhe Trespe')
     , (25, 2, 'Great Brome')
     , (25, 3, 'Bromus rígid')  
     , (25, 4, 'Brome rigide')
     , (25, 5, 'Squala')  
;
select * from TR_traduc
;
select count(*) from TR_traduc
;
analyze 
select HE.HE_ident
     , HE.HE_lib
     , TR.TR_lib
from  HE_herb HE
inner join TR_traduc TR
   on TR.HE_ident = HE.HE_ident
  and TR.LG_ident = 4
order by TR.TR_lib  
limit 20
;
 
analyze 
select HE.HE_ident
     , HE.HE_lib
     , TR.TR_lib
from  HE_herb HE
left join TR_traduc TR
   on TR.HE_ident = HE.HE_ident
  and TR.LG_ident = 4
order by TR.TR_lib  
limit 20  
;

Et le DB<>fiddle ICI

[SQLpro]

Merci pour votre réponse et vos tests.

Ha oui, la différence est flagrante... Probablement pas de solution efficace avec MySQL alors lorsqu'il commence à y avoir "pas mal" d'informations.

Pour information, la base sous MS SQL Server fait moins de 500 Mo de data... Vous pouvez donc utiliser la version gratuite Express...

Mais préférez la version Web dont le cout est de moins d'une vingtaine d'euros par mois...

Vous avez aussi la possbilité d'utiliser SQL Server on premise (dans votre SI physique) en mode "pay as you go" via Azure Arc... C'est tout nouveau. Si vous utilisez votre SGBDR moins de 40% du temps, par exemple uniquement de 9h à 17h les jours de semaine (donc off les weekend et jours fériés), vous allez payer l'équivalent de 50 % de la license qui est de l'ordre de 1 500 € par coeurs... vous seriez, pour un bicoeurs à environ 30 € par mois pour la version Standard (limitée à 200 Go de cache)....

Pourquoi de telles différences de perf...
1) l'utf8 de MySQmerde code tout caractères sur 3 octets, là ou SQL Server n'utilise qu'un seul octet !
Donc trois fois plus de volume pour traiter vos données...

2) MySQmerde ne supporte par le multithreding des requêtes... SQL Server fait du parallélisme massif et à tous les niveaux.... Requêtes, accès disques, etc...

3) MySQL est limité en terme d'index, ne connais pas les index INCLUDE, les index verticaux... J'ai même pas essayé l'indexation verticale COLUMSTORE de SQL Server pour ces tests, car elle ne donne de réels gains que si plusieurs millions de lignes, car elle stocke les données par paquets de 1 millions de ligne...

4) l'optimiseur de MySQL est une daube finie... Celui de SQL Server est actuellement le meilleur devant Oracle et IBM DB2...

[bdgdgdfg]

De mon côté, j'ai fait un essai avec un échantillon réduit : 25 noms scientifiques et 70 noms communs répartis dans 5 langues.

Merci beaucoup pour votre réponse et le temps pris afin d'effectuer des tests.

En effet, il n'y a donc probablement aucune "bonne" solution en restant sur MariaDB et un left join. La requête reste trop lente et n'est pas du tout adaptée pour la production.

[bdgdgdfg]

Pourquoi de telles différences de perf...
1) l'utf8 de MySQmerde code tout caractères sur 3 octets, là ou SQL Server n'utilise qu'un seul octet !
Donc trois fois plus de volume pour traiter vos données...

2) MySQmerde ne supporte par le multithreding des requêtes... SQL Server fait du parallélisme massif et à tous les niveaux.... Requêtes, accès disques, etc...

3) MySQL est limité en terme d'index, ne connais pas les index INCLUDE, les index verticaux... J'ai même pas essayé l'indexation verticale COLUMSTORE de SQL Server pour ces tests, car elle ne donne de réels gains que si plusieurs millions de lignes, car elle stocke les données par paquets de 1 millions de ligne...

4) l'optimiseur de MySQL est une daube finie... Celui de SQL Server est actuellement le meilleur devant Oracle et IBM DB2...

Merci beaucoup pour votre réponse et le temps pris à tout bien expliquer.

En effet, comme cité dans un précédent message, aucune solution donc via MariaDB pour que cette requête externe soit viable en production.

Je n'aurai donc aucun autre choix que de changer.

[escartefigue]

Pour rappel, quel que soit le SGBD retenu, il faudra tenir compte du point suivant :

À noter : pour des raisons historiques, dans la vraie vie, les noms scientifiques (latins) d'une même espèce animale comme végétale sont multiples. Il faudrait donc mettre en place une relation réflexive sur la table "herb", avec une notion de rang. Le rang 1 étant le nom officiel.