SELECT 
  a.variant_id, 
  a.option_id, 
  a.position, 
  a.modifier, 
  a.modifier_type, 
  a.weight_modifier, 
  a.weight_modifier_type, 
  b.variant_name, 
  IF(
    shared_option_variants.variant_id IS NOT NULL, 
    shared_option_variants.modifier, 
    a.modifier
  ) as modifier, 
  IF(
    shared_option_variants.variant_id IS NOT NULL, 
    shared_option_variants.modifier_type, 
    a.modifier_type
  ) as modifier_type 
FROM 
  cscart_product_option_variants as a 
  LEFT JOIN cscart_product_option_variants_descriptions as b ON a.variant_id = b.variant_id 
  AND b.lang_code = 'de' 
  LEFT JOIN cscart_ult_product_option_variants shared_option_variants ON shared_option_variants.variant_id = a.variant_id 
  AND shared_option_variants.company_id = 1 
WHERE 
  a.option_id IN (162, 254, 908, 926, 288, 290) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00102

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE a range status,option_id,option_id_2 option_id 6 29 Using index condition; Using filesort
1 SIMPLE b eq_ref PRIMARY PRIMARY 9 mahm3t_cs443.a.variant_id,const 1 Using where
1 SIMPLE shared_option_variants eq_ref PRIMARY,company_id PRIMARY 7 mahm3t_cs443.a.variant_id,const 1

Result

variant_id option_id position modifier modifier_type weight_modifier weight_modifier_type variant_name
7313 290 1 0.000 A 0.000 A 01 Silber
7304 290 2 0.000 A 0.000 A 02 Weiß
7315 290 3 0.000 A 0.000 A 03 Grau
7297 290 4 0.000 A 0.000 A 04 Dunkelbeige
7303 290 5 0.000 A 0.000 A 05 Beige
7309 290 7 0.000 A 0.000 A 07 Hellgrau
7307 290 8 0.000 A 0.000 A 08 Dunkelbraun
7306 290 9 0.000 A 0.000 A 09 Braun
4033 162 10 0.000 A 0.000 A weiß
7077 254 10 0.000 A 0.000 A weiß
7281 288 10 0.000 A 0.000 A PA-39
18133 908 10 0.000 A 0.000 A Gerade
18169 926 10 0.000 A 0.000 A Weiß
7298 290 13 0.000 A 0.000 A 13 Grün
7305 290 15 0.000 A 0.000 A 15 Cremeweiß
7310 290 16 0.000 A 0.000 A 16 Elfenbein
7299 290 19 0.000 A 0.000 A 19 Bordeauxrot
7078 254 20 0.000 A 0.000 A Winchester
7282 288 20 0.000 A 0.000 A PA-39p
7300 290 20 0.000 A 0.000 A 20 Schwarz
22146 162 20 0.000 A 0.000 A Winchester
7311 290 22 0.000 A 0.000 A 22 Mahagoni
7302 290 23 0.000 A 0.000 A 23 Anthrazit
7314 290 26 0.000 A 0.000 A 26 Stahlolive
7317 290 26 0.000 A 0.000 A 26 Stahlolive
7301 290 27 0.000 A 0.000 A 27 Basaltgrau
4035 162 30 0.000 A 0.000 A Golden Oak
7079 254 30 0.000 A 0.000 A Golden Oak
7283 288 30 0.000 A 0.000 A PA-45
7312 290 30 0.000 A 0.000 A 30 Nussbaum
18134 908 30 0.000 A 0.000 A Halbrund
18171 926 30 0.000 A 0.000 A Winchester
7308 290 34 0.000 A 0.000 A 34 Sumpfeiche
7284 288 40 0.000 A 0.000 A PA-45p
7316 290 48 0.000 A 0.000 A 48 Goldeiche
4037 162 50 0.000 A 0.000 A Nußbaum
7081 254 50 0.000 A 0.000 A Nußbaum
7285 288 50 0.000 A 0.000 A PA-52
18172 926 50 0.000 A 0.000 A Goldene Eiche
4038 162 60 0.000 A 0.000 A Mahagoni
7082 254 60 0.000 A 0.000 A Mahagoni
7286 288 60 0.000 A 0.000 A PA-52p
7083 254 70 0.000 A 0.000 A Dunkelgrau
7287 288 70 0.000 A 0.000 A PVC-37p
18173 926 70 0.000 A 0.000 A Nussbaum
22147 162 70 0.000 A 0.000 A Dark grey
7288 288 80 0.000 A 0.000 A PVC-52p
18174 926 90 0.000 A 0.000 A Sumpfeiche
18176 926 180 0.000 A 0.000 A Anthrazitgrau seidenglatt
18183 926 190 0.000 A 0.000 A Anthrazitgrau gebürstet (metallic)