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 (859, 1149, 535, 621, 860) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00130

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 36 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
15152 621 2 0.000 A 0.000 A 102 (RAL 7022)
14038 535 10 0.000 A 0.000 A 55x78 cm
14039 535 20 0.000 A 0.000 A 55x98 cm
15309 535 22 0.000 A 0.000 A 55x118 cm
14040 535 30 0.000 A 0.000 A 66x98 cm
14041 535 40 0.000 A 0.000 A 66x118 cm
14215 535 42 0.000 A 0.000 A 66x140 cm
14042 535 50 0.000 A 0.000 A 78x98 cm
14043 535 60 0.000 A 0.000 A 78x118 cm
14044 535 70 0.000 A 0.000 A 78x140 cm
14045 535 72 0.000 A 0.000 A 78x160 cm
14046 535 80 0.000 A 0.000 A 94x118 cm
14047 535 90 0.000 A 0.000 A 94x140 cm
15373 535 92 0.000 A 0.000 A 94x160 cm
14048 535 100 0.000 A 0.000 A 114x118 cm
15151 621 101 0.000 A 0.000 A 101 (RAL 7035)
17657 859 102 0.000 A 0.000 A CK02, C02, 102
17679 860 102 0.000 A 0.000 A CK02, C02, 102
17658 859 104 0.000 A 0.000 A CK04, C04, 104
17680 860 104 0.000 A 0.000 A CK04, C04, 104
17659 859 106 0.000 A 0.000 A CK06, C06
17681 860 106 0.000 A 0.000 A CK06, C06
14049 535 110 0.000 A 0.000 A 114x140 cm
14050 535 120 0.000 A 0.000 A 134x98 cm
17660 859 204 0.000 A 0.000 A FK04, F04, 204
17682 860 204 0.000 A 0.000 A FK04, F04, 204
17661 859 206 0.000 A 0.000 A FK06, F06, 206
17683 860 206 0.000 A 0.000 A FK06, F06, 206
17662 859 208 0.000 A 0.000 A FK08, F08
17684 860 208 0.000 A 0.000 A FK08, F08
17663 859 304 0.000 A 0.000 A MK04, M04, 304
17685 860 304 0.000 A 0.000 A MK04, M04, 304
17664 859 306 0.000 A 0.000 A MK06, M06, 306
17686 860 306 0.000 A 0.000 A MK06, M06, 306
17665 859 308 0.000 A 0.000 A MK08, M08, 308
17687 860 308 0.000 A 0.000 A MK08, M08, 308
17666 859 310 0.000 A 0.000 A MK10, M10. 310
17688 860 310 0.000 A 0.000 A MK10, M10. 310
17667 859 312 0.000 A 0.000 A MK12, M12, 312
17689 860 312 0.000 A 0.000 A MK12, M12, 312
17668 859 404 0.000 A 0.000 A PK04, P04, 404
17690 860 404 0.000 A 0.000 A PK04, P04, 404
17669 859 406 0.000 A 0.000 A PK06, P06, 406
17691 860 406 0.000 A 0.000 A PK06, P06, 406
17670 859 408 0.000 A 0.000 A PK08, P08, 408
17692 860 408 0.000 A 0.000 A PK08, P08, 408
17671 859 410 0.000 A 0.000 A PK10, P10, 410
17693 860 410 0.000 A 0.000 A PK10, P10, 410
17672 859 601 0.000 A 0.000 A SK01, S01
17694 860 601 0.000 A 0.000 A SK01, S01
17673 859 606 0.000 A 0.000 A SK06, S06, 606
17695 860 606 0.000 A 0.000 A SK06, S06, 606
17674 859 608 0.000 A 0.000 A SK08, S08, 608
17696 860 608 0.000 A 0.000 A SK08, S08, 608
17675 859 610 0.000 A 0.000 A SK10, S10, 610
17697 860 610 0.000 A 0.000 A SK10, S10, 610
17676 859 804 0.000 A 0.000 A UK04, U04, 804
17698 860 804 0.000 A 0.000 A UK04, U04, 804
17677 859 808 0.000 A 0.000 A UK08, U08, 808
17699 860 808 0.000 A 0.000 A UK08, U08, 808
17678 859 810 0.000 A 0.000 A UK10, U10, 810
17700 860 810 0.000 A 0.000 A UK10, U10, 810