SELECT 
  b.variant_name, 
  a.*, 
  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 b.variant_id = a.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 
  1 
  AND a.option_id IN (
    85480, 85210, 85209, 85219, 85132, 84770, 
    84545, 84544, 84539, 84317, 84312, 
    84311, 84310, 84309, 84303, 84215, 
    84214, 84691, 84690, 84688
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00098

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 40 Using index condition; Using filesort
1 SIMPLE b eq_ref PRIMARY PRIMARY 9 toolboxdigitalshop_dev.a.variant_id,const 1 Using where
1 SIMPLE shared_option_variants eq_ref PRIMARY,company_id PRIMARY 7 toolboxdigitalshop_dev.a.variant_id,const 1

Result

variant_name variant_id option_id position modifier modifier_type weight_modifier weight_modifier_type point_modifier point_modifier_type status
MP3 168964 84214 1 0.000 A 0.000 A 0.000 A A
MP3 168966 84215 1 0.000 A 0.000 A 0.000 A A
MP3 169142 84303 1 0.000 A 0.000 A 0.000 A A
MP3 169154 84309 1 0.000 A 0.000 A 0.000 A A
MP3 169156 84310 1 0.000 A 0.000 A 0.000 A A
MP3 169158 84311 1 0.000 A 0.000 A 0.000 A A
MP3 169160 84312 1 0.000 A 0.000 A 0.000 A A
MP3 169170 84317 1 0.000 A 0.000 A 0.000 A A
MP3 169614 84539 1 0.000 A 0.000 A 0.000 A A
MP3 169624 84544 1 0.000 A 0.000 A 0.000 A A
MP3 169626 84545 1 0.000 A 0.000 A 0.000 A A
MP3 169912 84688 1 0.000 A 0.000 A 0.000 A A
MP3 169916 84690 1 0.000 A 0.000 A 0.000 A A
MP3 169918 84691 1 0.000 A 0.000 A 0.000 A A
MP3 170076 84770 1 0.000 A 0.000 A 0.000 A A
MP3 170800 85132 1 0.000 A 0.000 A 0.000 A A
MP3 170954 85209 1 0.000 A 0.000 A 0.000 A A
MP3 170956 85210 1 0.000 A 0.000 A 0.000 A A
MP3 170974 85219 1 0.000 A 0.000 A 0.000 A A
MP3 171496 85480 1 0.000 A 0.000 A 0.000 A A
WAV 168965 84214 2 0.300 A 0.000 A 0.000 A A
WAV 168967 84215 2 0.300 A 0.000 A 0.000 A A
WAV 169143 84303 2 0.300 A 0.000 A 0.000 A A
WAV 169155 84309 2 0.300 A 0.000 A 0.000 A A
WAV 169157 84310 2 0.300 A 0.000 A 0.000 A A
WAV 169159 84311 2 0.300 A 0.000 A 0.000 A A
WAV 169161 84312 2 0.300 A 0.000 A 0.000 A A
WAV 169171 84317 2 0.300 A 0.000 A 0.000 A A
WAV 169615 84539 2 0.300 A 0.000 A 0.000 A A
WAV 169625 84544 2 0.300 A 0.000 A 0.000 A A
WAV 169627 84545 2 0.300 A 0.000 A 0.000 A A
WAV 169913 84688 2 0.300 A 0.000 A 0.000 A A
WAV 169917 84690 2 0.300 A 0.000 A 0.000 A A
WAV 169919 84691 2 0.300 A 0.000 A 0.000 A A
WAV 170077 84770 2 0.300 A 0.000 A 0.000 A A
WAV 170801 85132 2 0.300 A 0.000 A 0.000 A A
WAV 170955 85209 2 0.300 A 0.000 A 0.000 A A
WAV 170957 85210 2 0.300 A 0.000 A 0.000 A A
WAV 170975 85219 2 0.300 A 0.000 A 0.000 A A
WAV 171497 85480 2 0.300 A 0.000 A 0.000 A A