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 (
    82862, 82861, 82860, 82859, 82846, 82845, 
    82844, 82843, 82842, 82841, 82840, 
    82839, 82838, 82981, 82714, 82713, 
    82712, 82711, 82709
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00101

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		38	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	165954	82709	1	0.000	A	A	A	A
MP3	165958	82711	1	0.000	A	A	A	A
MP3	165960	82712	1	0.000	A	A	A	A
MP3	165962	82713	1	0.000	A	A	A	A
MP3	165964	82714	1	0.000	A	A	A	A
MP3	166212	82838	1	0.000	A	A	A	A
MP3	166214	82839	1	0.000	A	A	A	A
MP3	166216	82840	1	0.000	A	A	A	A
MP3	166218	82841	1	0.000	A	A	A	A
MP3	166220	82842	1	0.000	A	A	A	A
MP3	166222	82843	1	0.000	A	A	A	A
MP3	166224	82844	1	0.000	A	A	A	A
MP3	166226	82845	1	0.000	A	A	A	A
MP3	166228	82846	1	0.000	A	A	A	A
MP3	166254	82859	1	0.000	A	A	A	A
MP3	166256	82860	1	0.000	A	A	A	A
MP3	166258	82861	1	0.000	A	A	A	A
MP3	166260	82862	1	0.000	A	A	A	A
MP3	166498	82981	1	0.000	A	A	A	A
WAV	165955	82709	2	0.300	A	A	A	A
WAV	165959	82711	2	0.300	A	A	A	A
WAV	165961	82712	2	0.300	A	A	A	A
WAV	165963	82713	2	0.300	A	A	A	A
WAV	165965	82714	2	0.300	A	A	A	A
WAV	166213	82838	2	0.300	A	A	A	A
WAV	166215	82839	2	0.300	A	A	A	A
WAV	166217	82840	2	0.300	A	A	A	A
WAV	166219	82841	2	0.300	A	A	A	A
WAV	166221	82842	2	0.300	A	A	A	A
WAV	166223	82843	2	0.300	A	A	A	A
WAV	166225	82844	2	0.300	A	A	A	A
WAV	166227	82845	2	0.300	A	A	A	A
WAV	166229	82846	2	0.300	A	A	A	A
WAV	166255	82859	2	0.300	A	A	A	A
WAV	166257	82860	2	0.300	A	A	A	A
WAV	166259	82861	2	0.300	A	A	A	A
WAV	166261	82862	2	0.300	A	A	A	A
WAV	166499	82981	2	0.300	A	A	A	A