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 (
    83894, 83893, 83892, 83891, 83890, 83889, 
    83888, 83887, 83886, 83885, 83884, 
    83883, 83882, 83881, 83880, 83879, 
    83878, 83877, 83876, 83875, 83874, 
    83873, 83872, 84412, 84411, 84410, 
    84409, 84408, 84407, 84152, 84151, 
    84150, 84128, 84127, 84126, 84125, 
    83851, 83850, 84218, 84217
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00194

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	168236	83850	1	0.000	A	A	A	A
MP3	168238	83851	1	0.000	A	A	A	A
MP3	168280	83872	1	0.000	A	A	A	A
MP3	168282	83873	1	0.000	A	A	A	A
MP3	168284	83874	1	0.000	A	A	A	A
MP3	168286	83875	1	0.000	A	A	A	A
MP3	168288	83876	1	0.000	A	A	A	A
MP3	168290	83877	1	0.000	A	A	A	A
MP3	168292	83878	1	0.000	A	A	A	A
MP3	168294	83879	1	0.000	A	A	A	A
MP3	168296	83880	1	0.000	A	A	A	A
MP3	168298	83881	1	0.000	A	A	A	A
MP3	168300	83882	1	0.000	A	A	A	A
MP3	168302	83883	1	0.000	A	A	A	A
MP3	168304	83884	1	0.000	A	A	A	A
MP3	168306	83885	1	0.000	A	A	A	A
MP3	168308	83886	1	0.000	A	A	A	A
MP3	168310	83887	1	0.000	A	A	A	A
MP3	168312	83888	1	0.000	A	A	A	A
MP3	168314	83889	1	0.000	A	A	A	A
MP3	168316	83890	1	0.000	A	A	A	A
MP3	168318	83891	1	0.000	A	A	A	A
MP3	168320	83892	1	0.000	A	A	A	A
MP3	168322	83893	1	0.000	A	A	A	A
MP3	168324	83894	1	0.000	A	A	A	A
MP3	168786	84125	1	0.000	A	A	A	A
MP3	168788	84126	1	0.000	A	A	A	A
MP3	168790	84127	1	0.000	A	A	A	A
MP3	168792	84128	1	0.000	A	A	A	A
MP3	168836	84150	1	0.000	A	A	A	A
MP3	168838	84151	1	0.000	A	A	A	A
MP3	168840	84152	1	0.000	A	A	A	A
MP3	168970	84217	1	0.000	A	A	A	A
MP3	168972	84218	1	0.000	A	A	A	A
MP3	169350	84407	1	0.000	A	A	A	A
MP3	169352	84408	1	0.000	A	A	A	A
MP3	169354	84409	1	0.000	A	A	A	A
MP3	169356	84410	1	0.000	A	A	A	A
MP3	169358	84411	1	0.000	A	A	A	A
MP3	169360	84412	1	0.000	A	A	A	A
WAV	168237	83850	2	0.300	A	A	A	A
WAV	168239	83851	2	0.300	A	A	A	A
WAV	168281	83872	2	0.300	A	A	A	A
WAV	168283	83873	2	0.300	A	A	A	A
WAV	168285	83874	2	0.300	A	A	A	A
WAV	168287	83875	2	0.300	A	A	A	A
WAV	168289	83876	2	0.300	A	A	A	A
WAV	168291	83877	2	0.300	A	A	A	A
WAV	168293	83878	2	0.300	A	A	A	A
WAV	168295	83879	2	0.300	A	A	A	A
WAV	168297	83880	2	0.300	A	A	A	A
WAV	168299	83881	2	0.300	A	A	A	A
WAV	168301	83882	2	0.300	A	A	A	A
WAV	168303	83883	2	0.300	A	A	A	A
WAV	168305	83884	2	0.300	A	A	A	A
WAV	168307	83885	2	0.300	A	A	A	A
WAV	168309	83886	2	0.300	A	A	A	A
WAV	168311	83887	2	0.300	A	A	A	A
WAV	168313	83888	2	0.300	A	A	A	A
WAV	168315	83889	2	0.300	A	A	A	A
WAV	168317	83890	2	0.300	A	A	A	A
WAV	168319	83891	2	0.300	A	A	A	A
WAV	168321	83892	2	0.300	A	A	A	A
WAV	168323	83893	2	0.300	A	A	A	A
WAV	168325	83894	2	0.300	A	A	A	A
WAV	168787	84125	2	0.300	A	A	A	A
WAV	168789	84126	2	0.300	A	A	A	A
WAV	168791	84127	2	0.300	A	A	A	A
WAV	168793	84128	2	0.300	A	A	A	A
WAV	168837	84150	2	0.300	A	A	A	A
WAV	168839	84151	2	0.300	A	A	A	A
WAV	168841	84152	2	0.300	A	A	A	A
WAV	168971	84217	2	0.300	A	A	A	A
WAV	168973	84218	2	0.300	A	A	A	A
WAV	169351	84407	2	0.300	A	A	A	A
WAV	169353	84408	2	0.300	A	A	A	A
WAV	169355	84409	2	0.300	A	A	A	A
WAV	169357	84410	2	0.300	A	A	A	A
WAV	169359	84411	2	0.300	A	A	A	A
WAV	169361	84412	2	0.300	A	A	A	A