Sandbox

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 (
    82857, 82855, 82853, 82852, 82851, 82849, 
    82848, 82804, 82803, 82802, 82724, 
    82723, 82722, 82721, 82720, 82458, 
    82457, 82375, 82374, 82373, 82372, 
    82261, 83203, 83202, 83201, 83200, 
    83199, 83198, 82749, 82656, 83441, 
    83440, 82752, 82553, 82382, 82371, 
    82368, 82267, 82266, 82347
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00158

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		80	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_type	point_modifier_type	status
MP3	165058	82261	1	0.000	A	A	A	A
MP3	165068	82266	1	0.000	A	A	A	A
MP3	165070	82267	1	0.000	A	A	A	A
MP3	165230	82347	1	0.000	A	A	A	A
MP3	165272	82368	1	0.000	A	A	A	A
MP3	165278	82371	1	0.000	A	A	A	A
MP3	165280	82372	1	0.000	A	A	A	A
MP3	165282	82373	1	0.000	A	A	A	A
MP3	165284	82374	1	0.000	A	A	A	A
MP3	165286	82375	1	0.000	A	A	A	A
MP3	165300	82382	1	0.000	A	A	A	A
MP3	165450	82457	1	0.000	A	A	A	A
MP3	165452	82458	1	0.000	A	A	A	A
MP3	165642	82553	1	0.000	A	A	A	A
MP3	165848	82656	1	0.000	A	A	A	A
MP3	165976	82720	1	0.000	A	A	A	A
MP3	165978	82721	1	0.000	A	A	A	A
MP3	165980	82722	1	0.000	A	A	A	A
MP3	165982	82723	1	0.000	A	A	A	A
MP3	165984	82724	1	0.000	A	A	A	A
MP3	166034	82749	1	0.000	A	A	A	A
MP3	166040	82752	1	0.000	A	A	A	A
MP3	166140	82802	1	0.000	A	A	A	A
MP3	166142	82803	1	0.000	A	A	A	A
MP3	166144	82804	1	0.000	A	A	A	A
MP3	166232	82848	1	0.000	A	A	A	A
MP3	166234	82849	1	0.000	A	A	A	A
MP3	166238	82851	1	0.000	A	A	A	A
MP3	166240	82852	1	0.000	A	A	A	A
MP3	166242	82853	1	0.000	A	A	A	A
MP3	166246	82855	1	0.000	A	A	A	A
MP3	166250	82857	1	0.000	A	A	A	A
MP3	166932	83198	1	0.000	A	A	A	A
MP3	166934	83199	1	0.000	A	A	A	A
MP3	166936	83200	1	0.000	A	A	A	A
MP3	166938	83201	1	0.000	A	A	A	A
MP3	166940	83202	1	0.000	A	A	A	A
MP3	166942	83203	1	0.000	A	A	A	A
MP3	167416	83440	1	0.000	A	A	A	A
MP3	167418	83441	1	0.000	A	A	A	A
WAV	165059	82261	2	0.300	A	A	A	A
WAV	165069	82266	2	0.300	A	A	A	A
WAV	165071	82267	2	0.300	A	A	A	A
WAV	165231	82347	2	0.300	A	A	A	A
WAV	165273	82368	2	0.300	A	A	A	A
WAV	165279	82371	2	0.300	A	A	A	A
WAV	165281	82372	2	0.300	A	A	A	A
WAV	165283	82373	2	0.300	A	A	A	A
WAV	165285	82374	2	0.300	A	A	A	A
WAV	165287	82375	2	0.300	A	A	A	A
WAV	165301	82382	2	0.300	A	A	A	A
WAV	165451	82457	2	0.300	A	A	A	A
WAV	165453	82458	2	0.300	A	A	A	A
WAV	165643	82553	2	0.300	A	A	A	A
WAV	165849	82656	2	0.300	A	A	A	A
WAV	165977	82720	2	0.300	A	A	A	A
WAV	165979	82721	2	0.300	A	A	A	A
WAV	165981	82722	2	0.300	A	A	A	A
WAV	165983	82723	2	0.300	A	A	A	A
WAV	165985	82724	2	0.300	A	A	A	A
WAV	166035	82749	2	0.300	A	A	A	A
WAV	166041	82752	2	0.300	A	A	A	A
WAV	166141	82802	2	0.300	A	A	A	A
WAV	166143	82803	2	0.300	A	A	A	A
WAV	166145	82804	2	0.300	A	A	A	A
WAV	166233	82848	2	0.300	A	A	A	A
WAV	166235	82849	2	0.300	A	A	A	A
WAV	166239	82851	2	0.300	A	A	A	A
WAV	166241	82852	2	0.300	A	A	A	A
WAV	166243	82853	2	0.300	A	A	A	A
WAV	166247	82855	2	0.300	A	A	A	A
WAV	166251	82857	2	0.300	A	A	A	A
WAV	166933	83198	2	0.300	A	A	A	A
WAV	166935	83199	2	0.300	A	A	A	A
WAV	166937	83200	2	0.300	A	A	A	A
WAV	166939	83201	2	0.300	A	A	A	A
WAV	166941	83202	2	0.300	A	A	A	A
WAV	166943	83203	2	0.300	A	A	A	A
WAV	167417	83440	2	0.300	A	A	A	A
WAV	167419	83441	2	0.300	A	A	A	A