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 (
    753, 1359, 752, 1357, 754, 1360, 1361, 
    755, 1358, 759, 1362, 757, 758
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00114

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 74 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
24260 1357 10 0.000 A 0.000 A 0.5 mm nach EN 14195
24262 1358 10 0.000 A 0.000 A 0,5 mm nach EN 14195
24265 1359 10 0.000 A 0.000 A 0.5 mm nach EN 14195
24266 1360 10 0.000 A 0.000 A 3.0 m
24268 1361 10 0.000 A 0.000 A 0.5 mm nach EN 14195
24273 1362 10 0.000 A 0.000 A 2.0 mm
24261 1357 20 0.000 A 0.000 A 0.6 mm nach DIN 18182
24263 1358 20 0.000 A 0.000 A 0,6 mm nach DIN 18182
24264 1359 20 0.000 A 0.000 A 0.6 mm nach DIN 18182
24267 1360 20 0.000 A 0.000 A 4.0 m
24269 1361 20 0.000 A 0.000 A 0.6 mm nach DIN 18182
24272 1362 20 0.000 A 0.000 A 1.8 mm
16331 753 26 0.000 A 0.000 A 2.6 m
16334 754 26 0.000 A 0.000 A 2.6 m
16332 753 30 0.000 A 0.000 A 3.0 m
16335 754 30 0.000 A 0.000 A 3.0 m
16346 758 30 0.000 A 0.000 A 3.0 m
16349 759 30 0.000 A 0.000 A 3.0 m
16347 758 35 0.000 A 0.000 A 3.5 m
23604 753 35 0.000 A 0.000 A 3.5 m
16333 753 40 0.000 A 0.000 A 4.0 m
16336 754 40 0.000 A 0.000 A 4.0 m
16348 758 40 0.000 A 0.000 A 4.0 m
16350 759 40 0.000 A 0.000 A 4.0 m
16328 752 50 0.000 A 0.000 A CW 50 mm
16337 755 50 0.000 A 0.000 A 50 mm
16343 757 50 0.000 A 0.000 A 50 mm
16329 752 75 0.000 A 0.000 A CW 75 mm
16338 755 75 0.000 A 0.000 A 75 mm
16344 757 75 0.000 A 0.000 A 75 mm
16330 752 100 0.000 A 0.000 A CW 100 mm
16339 755 100 0.000 A 0.000 A 100 mm
16345 757 100 0.000 A 0.000 A 100 mm
24270 757 125 0.000 A 0.000 A 125 mm
24271 757 150 0.000 A 0.000 A 150 mm