SELECT 
  cscart_product_feature_variants.variant_id, 
  cscart_product_feature_variants.position, 
  cscart_product_feature_variant_descriptions.variant 
FROM 
  cscart_product_feature_variants 
  LEFT JOIN cscart_product_feature_variant_descriptions ON cscart_product_feature_variants.variant_id = cscart_product_feature_variant_descriptions.variant_id 
  AND cscart_product_feature_variant_descriptions.lang_code = 'fr' 
WHERE 
  cscart_product_feature_variants.variant_id IN (
    95, 96, 98, 99, 100, 101, 103, 107, 108, 
    109, 145, 240, 6273
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00045

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_product_feature_variants range PRIMARY PRIMARY 3 13 Using index condition; Using temporary; Using filesort
1 SIMPLE cscart_product_feature_variant_descriptions eq_ref PRIMARY PRIMARY 9 mahm3t_cs443.cscart_product_feature_variants.variant_id,const 1 Using where

Result

variant_id position variant
6273 1 pivot central (poignée inférieure)
95 10 double vitrage
145 10 Fenêtres de toit
107 10 manuel
101 10 pivot central (barre supérieure)
108 20 électrique
99 20 PVC
96 20 triple vitrage
103 30 pivot / à projection
109 30 solaire
98 40 Bois | laque transparente
240 40 VELUX
100 60 Bois | peint en blanc