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 (
    82794, 82793, 82792, 82791, 82340, 82101, 
    82100, 82099, 82098, 82097, 82096, 
    82095, 82094, 82093, 82092, 82048, 
    82047, 81850, 81849, 82337, 82790, 
    82789, 82386, 82385, 82354, 82353, 
    82156, 81861, 81777, 81776, 81775, 
    81761, 81719, 82528, 82527, 82526, 
    82391, 82390, 82389, 82388
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00141

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	163974	81719	1	0.000	A	A	A	A
MP3	164058	81761	1	0.000	A	A	A	A
MP3	164086	81775	1	0.000	A	A	A	A
MP3	164088	81776	1	0.000	A	A	A	A
MP3	164090	81777	1	0.000	A	A	A	A
MP3	164234	81849	1	0.000	A	A	A	A
MP3	164236	81850	1	0.000	A	A	A	A
MP3	164258	81861	1	0.000	A	A	A	A
MP3	164630	82047	1	0.000	A	A	A	A
MP3	164632	82048	1	0.000	A	A	A	A
MP3	164720	82092	1	0.000	A	A	A	A
MP3	164722	82093	1	0.000	A	A	A	A
MP3	164724	82094	1	0.000	A	A	A	A
MP3	164726	82095	1	0.000	A	A	A	A
MP3	164728	82096	1	0.000	A	A	A	A
MP3	164730	82097	1	0.000	A	A	A	A
MP3	164732	82098	1	0.000	A	A	A	A
MP3	164734	82099	1	0.000	A	A	A	A
MP3	164736	82100	1	0.000	A	A	A	A
MP3	164738	82101	1	0.000	A	A	A	A
MP3	164848	82156	1	0.000	A	A	A	A
MP3	165210	82337	1	0.000	A	A	A	A
MP3	165216	82340	1	0.000	A	A	A	A
MP3	165242	82353	1	0.000	A	A	A	A
MP3	165244	82354	1	0.000	A	A	A	A
MP3	165306	82385	1	0.000	A	A	A	A
MP3	165308	82386	1	0.000	A	A	A	A
MP3	165312	82388	1	0.000	A	A	A	A
MP3	165314	82389	1	0.000	A	A	A	A
MP3	165316	82390	1	0.000	A	A	A	A
MP3	165318	82391	1	0.000	A	A	A	A
MP3	165588	82526	1	0.000	A	A	A	A
MP3	165590	82527	1	0.000	A	A	A	A
MP3	165592	82528	1	0.000	A	A	A	A
MP3	166114	82789	1	0.000	A	A	A	A
MP3	166116	82790	1	0.000	A	A	A	A
MP3	166118	82791	1	0.000	A	A	A	A
MP3	166120	82792	1	0.000	A	A	A	A
MP3	166122	82793	1	0.000	A	A	A	A
MP3	166124	82794	1	0.000	A	A	A	A
WAV	163975	81719	2	0.300	A	A	A	A
WAV	164059	81761	2	0.300	A	A	A	A
WAV	164087	81775	2	0.300	A	A	A	A
WAV	164089	81776	2	0.300	A	A	A	A
WAV	164091	81777	2	0.300	A	A	A	A
WAV	164235	81849	2	0.300	A	A	A	A
WAV	164237	81850	2	0.300	A	A	A	A
WAV	164259	81861	2	0.300	A	A	A	A
WAV	164631	82047	2	0.300	A	A	A	A
WAV	164633	82048	2	0.300	A	A	A	A
WAV	164721	82092	2	0.300	A	A	A	A
WAV	164723	82093	2	0.300	A	A	A	A
WAV	164725	82094	2	0.300	A	A	A	A
WAV	164727	82095	2	0.300	A	A	A	A
WAV	164729	82096	2	0.300	A	A	A	A
WAV	164731	82097	2	0.300	A	A	A	A
WAV	164733	82098	2	0.300	A	A	A	A
WAV	164735	82099	2	0.300	A	A	A	A
WAV	164737	82100	2	0.300	A	A	A	A
WAV	164739	82101	2	0.300	A	A	A	A
WAV	164849	82156	2	0.300	A	A	A	A
WAV	165211	82337	2	0.300	A	A	A	A
WAV	165217	82340	2	0.300	A	A	A	A
WAV	165243	82353	2	0.300	A	A	A	A
WAV	165245	82354	2	0.300	A	A	A	A
WAV	165307	82385	2	0.300	A	A	A	A
WAV	165309	82386	2	0.300	A	A	A	A
WAV	165313	82388	2	0.300	A	A	A	A
WAV	165315	82389	2	0.300	A	A	A	A
WAV	165317	82390	2	0.300	A	A	A	A
WAV	165319	82391	2	0.300	A	A	A	A
WAV	165589	82526	2	0.300	A	A	A	A
WAV	165591	82527	2	0.300	A	A	A	A
WAV	165593	82528	2	0.300	A	A	A	A
WAV	166115	82789	2	0.300	A	A	A	A
WAV	166117	82790	2	0.300	A	A	A	A
WAV	166119	82791	2	0.300	A	A	A	A
WAV	166121	82792	2	0.300	A	A	A	A
WAV	166123	82793	2	0.300	A	A	A	A
WAV	166125	82794	2	0.300	A	A	A	A