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 (1324, 1326, 1325, 1364) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00090

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 201 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
24016 1324 10 0.000 A 0.000 A Standard
24017 1324 20 0.000 A 0.000 A Thermo (zusätzliche Wärmedämmung)
24300 1364 20 0.000 A 0.000 A 55x98 cm
24299 1364 22 0.000 A 0.000 A 55x118 cm
24298 1364 30 0.000 A 0.000 A 66x98 cm
24291 1364 40 0.000 A 0.000 A 66x118 cm
24290 1364 42 0.000 A 0.000 A 66x140 cm
24292 1364 50 0.000 A 0.000 A 78x98 cm
24297 1364 60 0.000 A 0.000 A 78x118 cm
24296 1364 70 0.000 A 0.000 A 78x140 cm
24295 1364 72 0.000 A 0.000 A 78x160 cm
24294 1364 80 0.000 A 0.000 A 94x118 cm
24293 1364 90 0.000 A 0.000 A 94x140 cm
24046 1326 100 0.000 A 0.000 A EZV-A AX00 für Dachziegel oder Profilbleche bis zu 45mm Profilhöhe.
24289 1364 100 0.000 A 0.000 A 114x118 cm
24042 1326 105 0.000 A 0.000 A EHV-AT für Dachziegel oder Profilbleche bis zu 90mm Profilhöhe
24043 1326 110 0.000 A 0.000 A ESV AX00 für Dachpappe, Bitumenschindeln bis zu 10mm (2x5mm)
24303 1364 110 0.000 A 0.000 A 114x140 cm
24040 1326 115 0.000 A 0.000 A ESV-T AX00 für Dachpappe, Bitumenschindeln bis 10mm (2x5mm)
24302 1364 120 0.000 A 0.000 A 134x98 cm
24039 1326 135 0.000 A 0.000 A EZV-FT AX00 für flache Dachziegel
24038 1326 155 0.000 A 0.000 A EBV-PT für Paneelblech mit Stehfalz
24018 1325 507 0.000 A 0.000 A 55x78 cm
24019 1325 509 0.000 A 0.000 A 55x98 cm
24020 1325 511 0.000 A 0.000 A 55x118 cm
24021 1325 609 0.000 A 0.000 A 66x98 cm
24022 1325 611 0.000 A 0.000 A 66x118 cm
24023 1325 614 0.000 A 0.000 A 66x140 cm
24024 1325 709 0.000 A 0.000 A 78x98 cm
24025 1325 711 0.000 A 0.000 A 78x118 cm
24026 1325 714 0.000 A 0.000 A 78x140 cm
24027 1325 716 0.000 A 0.000 A 78x160 cm
24028 1325 718 0.000 A 0.000 A 78x180 cm
24029 1325 720 0.000 A 0.000 A 78x206 cm
24030 1325 911 0.000 A 0.000 A 94x118 cm
24031 1325 914 0.000 A 0.000 A 94x140 cm
24032 1325 916 0.000 A 0.000 A 94x160 cm
24033 1325 918 0.000 A 0.000 A 94x180 cm
24034 1325 920 0.000 A 0.000 A 94x206 cm
24035 1325 1111 0.000 A 0.000 A 114x118 cm
24036 1325 1114 0.000 A 0.000 A 114x140 cm
24037 1325 1309 0.000 A 0.000 A 134x98 cm