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 (
    95, 96, 98, 99, 100, 103, 107, 128, 129, 
    145, 146, 218, 240, 6297
  ) 
ORDER BY 
  cscart_product_feature_variants.position ASC, 
  cscart_product_feature_variant_descriptions.variant ASC

Query time 0.00152

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_product_feature_variants range PRIMARY PRIMARY 3 14 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
6297 0 Seitlich öffnendes
218 5 1-fach
95 10 2-fach
145 10 Dachfenster
128 10 FAKRO
107 10 Manuell
96 20 3-fach
146 20 Ausstiegsfenster
99 20 Kunststoff
103 30 Klapp-Schwing
129 30 Skylight (ARBONIA Schweiz)
98 40 Holz klar lackiert
240 40 VELUX
100 60 Holz weiß lackiert