MariaDB 10.9

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- 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  
;

Records: 25  Duplicates: 0  Warnings: 0

HE_ident	HE_lib	HE_cre	HE_maj
1	Poa annua	2024-03-08 10:09:31	2024-03-08 10:09:31
2	Cynodon dactylon	2024-03-08 10:09:31	2024-03-08 10:09:31
3	Lolium perenne	2024-03-08 10:09:31	2024-03-08 10:09:31
4	Bellardiochloa variegata	2024-03-08 10:09:31	2024-03-08 10:09:31
5	Arrhenatherum elatius	2024-03-08 10:09:31	2024-03-08 10:09:31
6	Hordeum bulbosum	2024-03-08 10:09:31	2024-03-08 10:09:31
7	Festuca cyrnea	2024-03-08 10:09:31	2024-03-08 10:09:31
8	Lolium rigidum lepturoides	2024-03-08 10:09:31	2024-03-08 10:09:31
9	Pucinellia capillaris	2024-03-08 10:09:31	2024-03-08 10:09:31
10	Puccinellia distans	2024-03-08 10:09:31	2024-03-08 10:09:31
11	Puccinellia fasciculata	2024-03-08 10:09:31	2024-03-08 10:09:31
12	Bromopsis benekenii	2024-03-08 10:09:31	2024-03-08 10:09:31
13	Anisantha diandra	2024-03-08 10:09:31	2024-03-08 10:09:31
14	Bromopsis pannonica	2024-03-08 10:09:31	2024-03-08 10:09:31
15	Bromus hordeaceus jansenii	2024-03-08 10:09:31	2024-03-08 10:09:31
16	Bromus japonicus subsquarrosus	2024-03-08 10:09:31	2024-03-08 10:09:31
17	Bromus pseudosecalinus	2024-03-08 10:09:31	2024-03-08 10:09:31
18	Bromus pseudothominei	2024-03-08 10:09:31	2024-03-08 10:09:31
19	Bromus × gusuleacii	2024-03-08 10:09:31	2024-03-08 10:09:31
20	Bromus × hannoverianus	2024-03-08 10:09:31	2024-03-08 10:09:31
21	Bromus × karlobagensis	2024-03-08 10:09:31	2024-03-08 10:09:31
22	Bromus × laagei	2024-03-08 10:09:31	2024-03-08 10:09:31
23	Anisantha fasciculata	2024-03-08 10:09:31	2024-03-08 10:09:31
24	Anisantha madritensis	2024-03-08 10:09:31	2024-03-08 10:09:31
25	Anisantha rigida	2024-03-08 10:09:31	2024-03-08 10:09:31

Records: 5  Duplicates: 0  Warnings: 0

LG_ident	LG_code	LG_lib
1	DE	Deutsch
2	EN	English
3	ES	Español
4	FR	Français
5	IT	Italiano

Records: 70  Duplicates: 0  Warnings: 0

HE_ident	LG_ident	TR_lib
1	1	Einjähriges Rispengras
1	2	Annual Bluegrass
1	3	Espiguilla anual
1	4	Pâturin annuel
1	5	Fienarola annuale
2	1	Hundszahngras
2	2	Bermuda Grass
2	3	Grama común
2	4	Chiendent pied de poule
2	5	Gramegna
3	1	Ausdauerndes Weidelgras
3	2	Common Ray-grass
3	3	Raigrás
3	4	Ray-grass anglais
4	1	Violettes Rispengras
4	2	Violet Meadowgrass
4	3	Poa violácea
4	4	Faux-Paturin violacé
5	1	Französiches Raigras
5	2	False Oat-grass
5	3	Avena descollada
5	4	Fromental
5	5	Avena maggiore
6	2	Bulbous Barley
6	4	Orge bulbeuse
6	5	Orzo bulboso
7	4	Fétuque
7	5	Festuca di Corsica
8	4	Ivraie
8	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

count(*)
70

id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
1	SIMPLE	TR	ref	PRIMARY,TR_IX02,TR_FK02	TR_FK02	2	const	24	20.00	100.00	100.00	Using where; Using filesort
1	SIMPLE	HE	eq_ref	PRIMARY	PRIMARY	8	fiddle.TR.HE_ident	1	1.00	100.00	100.00

id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
1	SIMPLE	HE	index	null	HE_UK01	1022	null	25	25.00	100.00	100.00	Using index; Using temporary; Using filesort
1	SIMPLE	TR	eq_ref	PRIMARY,TR_IX02,TR_FK02	PRIMARY	10	fiddle.HE.HE_ident,const	1	0.96	100.00	100.00