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 = 'en' 
  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 = 2 
WHERE 
  a.option_id IN (288, 290) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00205

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 17 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
7281 288 10 0.000 A 0.000 A PA-39
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
7282 288 20 0.000 A 0.000 A PA-39p
7300 290 20 0.000 A 0.000 A 20 Schwarz
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
7283 288 30 0.000 A 0.000 A PA-45
7312 290 30 0.000 A 0.000 A 30 Nussbaum
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
7285 288 50 0.000 A 0.000 A PA-52
7286 288 60 0.000 A 0.000 A PA-52p
7287 288 70 0.000 A 0.000 A PVC-37p
7288 288 80 0.000 A 0.000 A PVC-52p