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 (
    668, 1360, 1361, 755, 1290, 1358, 759, 
    669, 670, 671
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00144

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 71 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
24262 1358 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
23644 1290 12 0.000 A 0.000 A 12 mm
23645 1290 15 0.000 A 0.000 A 15 mm
23646 1290 18 0.000 A 0.000 A 18 mm
24263 1358 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
23647 1290 22 0.000 A 0.000 A 22 mm
15555 669 25 0.000 A 0.000 A 25 mm
15564 670 25 0.000 A 0.000 A 25 mm
15572 671 25 0.000 A 0.000 A 25 mm
23648 1290 25 0.000 A 0.000 A 25 mm
15549 668 30 0.000 A 0.000 A 30 mm
16349 759 30 0.000 A 0.000 A 3.0 m
15556 669 35 0.000 A 0.000 A 35 mm
15565 670 35 0.000 A 0.000 A 35 mm
15573 671 35 0.000 A 0.000 A 35 mm
16350 759 40 0.000 A 0.000 A 4.0 m
15557 669 45 0.000 A 0.000 A 45 mm
15566 670 45 0.000 A 0.000 A 45 mm
15574 671 45 0.000 A 0.000 A 45 mm
15550 668 50 0.000 A 0.000 A 50 mm
16337 755 50 0.000 A 0.000 A 50 mm
15558 669 55 0.000 A 0.000 A 55 mm
15567 670 55 0.000 A 0.000 A 55 mm
15551 668 70 0.000 A 0.000 A 75 mm
15559 669 70 0.000 A 0.000 A 70 mm
15568 670 70 0.000 A 0.000 A 70 mm
16338 755 75 0.000 A 0.000 A 75 mm
15560 669 90 0.000 A 0.000 A 90 mm
15569 670 90 0.000 A 0.000 A 90 mm
15552 668 95 0.000 A 0.000 A 95 mm
15561 669 100 0.000 A 0.000 A 100 mm
15570 670 100 0.000 A 0.000 A 100 mm
16339 755 100 0.000 A 0.000 A 100 mm
15562 669 110 0.000 A 0.000 A 110 mm
15563 669 120 0.000 A 0.000 A 120 mm
15571 670 120 0.000 A 0.000 A 120 mm