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 = 1 
WHERE 
  a.option_id IN (
    436, 1288, 439, 479, 437, 631, 438, 294, 
    635, 293
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00109

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 48 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
7320 293 0 0.000 A 0.000 A for summer
11836 436 0 0.000 A 0.000 A 175 mm
11837 436 0 0.000 A 0.000 A 240 mm
11838 436 0 0.000 A 0.000 A 300 mm
11839 436 0 0.000 A 0.000 A 365 mm
11840 436 0 0.000 A 0.000 A 420 mm
11845 438 0 0.000 A 0.000 A 115 mm
11846 438 0 0.000 A 0.000 A 175 mm
11847 438 0 0.000 A 0.000 A 240 mm
11848 438 0 0.000 A 0.000 A 300 mm
11849 438 0 0.000 A 0.000 A 365 mm
11850 439 0 0.000 A 0.000 A 150 mm
11851 439 0 0.000 A 0.000 A 175 mm
11852 439 0 0.000 A 0.000 A 200 mm
11853 439 0 0.000 A 0.000 A 240 mm
11854 439 0 0.000 A 0.000 A 300 mm
11855 439 0 0.000 A 0.000 A 365 mm
11856 439 0 0.000 A 0.000 A 420 mm
11858 293 0 0.000 A 0.000 A for winter
12597 479 0 0.000 A 0.000 A 365 mm
12598 479 0 0.000 A 0.000 A 480 mm
15239 631 0 0.000 A 0.000 A 240 mm
15240 631 0 0.000 A 0.000 A 300 mm
23633 1288 0 0.000 A 0.000 A 175 mm
23634 1288 0 0.000 A 0.000 A 240 mm
23635 1288 0 0.000 A 0.000 A 300 mm
23636 1288 0 0.000 A 0.000 A 365 mm
23637 1288 0 0.000 A 0.000 A 420 mm
7378 294 10 0.000 A 0.000 A 115 x 125 x 1250 mm
7379 294 20 0.000 A 0.000 A 115 x 125 x 1500 mm
7380 294 30 0.000 A 0.000 A 115 x 125 x 2000 mm
7381 294 40 0.000 A 0.000 A 115 x 125 x 2500 mm
7382 294 50 0.000 A 0.000 A 115 x 125 x 3000 mm
12616 437 50 0.000 A 0.000 A 50 mm
15259 635 50 0.000 A 0.000 A 50 mm
7383 294 70 0.000 A 0.000 A 175 x 125 x 1250 mm
11841 437 75 0.000 A 0.000 A 75 mm
15260 635 75 0.000 A 0.000 A 75 mm
7384 294 80 0.000 A 0.000 A 175 x 125 x 1500 mm
7385 294 90 0.000 A 0.000 A 175 x 125 x 2000 mm
7386 294 100 0.000 A 0.000 A 175 x 125 x 2500 mm
11842 437 100 0.000 A 0.000 A 100 mm
15261 635 100 0.000 A 0.000 A 100 mm
7387 294 110 0.000 A 0.000 A 175 x 125 x 3000 mm
15262 635 115 0.000 A 0.000 A 115 mm
11843 437 150 0.000 A 0.000 A 150 mm
15263 635 150 0.000 A 0.000 A 150 mm
15264 635 175 0.000 A 0.000 A 175 mm
11844 437 200 0.000 A 0.000 A 200 mm
15265 635 200 0.000 A 0.000 A 200 mm
15266 635 240 0.000 A 0.000 A 240 mm
15267 635 300 0.000 A 0.000 A 300 mm
15268 635 365 0.000 A 0.000 A 365 mm
15269 635 400 0.000 A 0.000 A 400 mm