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 = 'de' 
WHERE 
  cscart_product_feature_variants.variant_id IN (
    123, 6145, 6146, 6147, 6148, 6149, 6150, 
    6151, 6152, 6153, 6182, 6184, 6185, 
    6186, 6188, 6189
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00057

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_product_feature_variants range PRIMARY PRIMARY 3 16 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
6182 1 1 - 15 mm
6185 3 3 - 30 mm
6186 5 5 - 30 mm
6189 10 10 - 80 mm
6147 10 Innen (Trocken)
6145 10 Selbstnivellierende
6150 10 Unter Fliesen
6184 11 10 - 100 mm
6148 20 Innen (Feuchträume)
6146 20 Standard
6151 20 Unter Parkett
6188 25 25 - 60 mm
6149 30 Außen
6152 30 Unter Holz, Teppich
6153 40 Auf Fußbodenheizung
123 100 Atlas