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 (
    82764, 82763, 82762, 82761, 82760, 82759, 
    82758, 82757, 82756, 82755, 82751, 
    82750, 82525, 82409, 82408, 82407, 
    82406, 83274, 83273, 83272, 82710, 
    83183, 83182, 83181, 83061, 83060, 
    83059, 83058, 83057, 83056, 82439, 
    82356, 83278, 83277, 83276, 83275, 
    83225, 83224, 83223, 82815
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00105

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	165248	82356	1	0.000	A	A	A	A
MP3	165348	82406	1	0.000	A	A	A	A
MP3	165350	82407	1	0.000	A	A	A	A
MP3	165352	82408	1	0.000	A	A	A	A
MP3	165354	82409	1	0.000	A	A	A	A
MP3	165414	82439	1	0.000	A	A	A	A
MP3	165586	82525	1	0.000	A	A	A	A
MP3	165956	82710	1	0.000	A	A	A	A
MP3	166036	82750	1	0.000	A	A	A	A
MP3	166038	82751	1	0.000	A	A	A	A
MP3	166046	82755	1	0.000	A	A	A	A
MP3	166048	82756	1	0.000	A	A	A	A
MP3	166050	82757	1	0.000	A	A	A	A
MP3	166052	82758	1	0.000	A	A	A	A
MP3	166054	82759	1	0.000	A	A	A	A
MP3	166056	82760	1	0.000	A	A	A	A
MP3	166058	82761	1	0.000	A	A	A	A
MP3	166060	82762	1	0.000	A	A	A	A
MP3	166062	82763	1	0.000	A	A	A	A
MP3	166064	82764	1	0.000	A	A	A	A
MP3	166166	82815	1	0.000	A	A	A	A
MP3	166648	83056	1	0.000	A	A	A	A
MP3	166650	83057	1	0.000	A	A	A	A
MP3	166652	83058	1	0.000	A	A	A	A
MP3	166654	83059	1	0.000	A	A	A	A
MP3	166656	83060	1	0.000	A	A	A	A
MP3	166658	83061	1	0.000	A	A	A	A
MP3	166898	83181	1	0.000	A	A	A	A
MP3	166900	83182	1	0.000	A	A	A	A
MP3	166902	83183	1	0.000	A	A	A	A
MP3	166982	83223	1	0.000	A	A	A	A
MP3	166984	83224	1	0.000	A	A	A	A
MP3	166986	83225	1	0.000	A	A	A	A
MP3	167080	83272	1	0.000	A	A	A	A
MP3	167082	83273	1	0.000	A	A	A	A
MP3	167084	83274	1	0.000	A	A	A	A
MP3	167086	83275	1	0.000	A	A	A	A
MP3	167088	83276	1	0.000	A	A	A	A
MP3	167090	83277	1	0.000	A	A	A	A
MP3	167092	83278	1	0.000	A	A	A	A
WAV	165249	82356	2	0.300	A	A	A	A
WAV	165349	82406	2	0.300	A	A	A	A
WAV	165351	82407	2	0.300	A	A	A	A
WAV	165353	82408	2	0.300	A	A	A	A
WAV	165355	82409	2	0.300	A	A	A	A
WAV	165415	82439	2	0.300	A	A	A	A
WAV	165587	82525	2	0.300	A	A	A	A
WAV	165957	82710	2	0.300	A	A	A	A
WAV	166037	82750	2	0.300	A	A	A	A
WAV	166039	82751	2	0.300	A	A	A	A
WAV	166047	82755	2	0.300	A	A	A	A
WAV	166049	82756	2	0.300	A	A	A	A
WAV	166051	82757	2	0.300	A	A	A	A
WAV	166053	82758	2	0.300	A	A	A	A
WAV	166055	82759	2	0.300	A	A	A	A
WAV	166057	82760	2	0.300	A	A	A	A
WAV	166059	82761	2	0.300	A	A	A	A
WAV	166061	82762	2	0.300	A	A	A	A
WAV	166063	82763	2	0.300	A	A	A	A
WAV	166065	82764	2	0.300	A	A	A	A
WAV	166167	82815	2	0.300	A	A	A	A
WAV	166649	83056	2	0.300	A	A	A	A
WAV	166651	83057	2	0.300	A	A	A	A
WAV	166653	83058	2	0.300	A	A	A	A
WAV	166655	83059	2	0.300	A	A	A	A
WAV	166657	83060	2	0.300	A	A	A	A
WAV	166659	83061	2	0.300	A	A	A	A
WAV	166899	83181	2	0.300	A	A	A	A
WAV	166901	83182	2	0.300	A	A	A	A
WAV	166903	83183	2	0.300	A	A	A	A
WAV	166983	83223	2	0.300	A	A	A	A
WAV	166985	83224	2	0.300	A	A	A	A
WAV	166987	83225	2	0.300	A	A	A	A
WAV	167081	83272	2	0.300	A	A	A	A
WAV	167083	83273	2	0.300	A	A	A	A
WAV	167085	83274	2	0.300	A	A	A	A
WAV	167087	83275	2	0.300	A	A	A	A
WAV	167089	83276	2	0.300	A	A	A	A
WAV	167091	83277	2	0.300	A	A	A	A
WAV	167093	83278	2	0.300	A	A	A	A