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 (128, 129, 130, 240, 6124, 6131) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00041

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_product_feature_variants range PRIMARY PRIMARY 3 6 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
128 10 FAKRO
130 20 Optilight
129 30 Skylight
240 40 VELUX
6131 42 RoofLITE+
6124 55 Roto