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 (
    83351, 83350, 83349, 83348, 83347, 83346, 
    83345, 83343, 83342, 83341, 83339, 
    83338, 83337, 83335, 83334, 83333, 
    83332, 83331, 83913, 96687
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00118

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		40	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	167198	83331	1	0.000	A	A	A	A
MP3	167200	83332	1	0.000	A	A	A	A
MP3	167202	83333	1	0.000	A	A	A	A
MP3	167204	83334	1	0.000	A	A	A	A
MP3	167206	83335	1	0.000	A	A	A	A
MP3	167210	83337	1	0.000	A	A	A	A
MP3	167212	83338	1	0.000	A	A	A	A
MP3	167214	83339	1	0.000	A	A	A	A
MP3	167218	83341	1	0.000	A	A	A	A
MP3	167220	83342	1	0.000	A	A	A	A
MP3	167222	83343	1	0.000	A	A	A	A
MP3	167226	83345	1	0.000	A	A	A	A
MP3	167228	83346	1	0.000	A	A	A	A
MP3	167230	83347	1	0.000	A	A	A	A
MP3	167232	83348	1	0.000	A	A	A	A
MP3	167234	83349	1	0.000	A	A	A	A
MP3	167236	83350	1	0.000	A	A	A	A
MP3	167238	83351	1	0.000	A	A	A	A
MP3	168362	83913	1	0.000	A	A	A	A
MP3	193910	96687	1	0.000	A	A	A	A
WAV	167199	83331	2	0.300	A	A	A	A
WAV	167201	83332	2	0.300	A	A	A	A
WAV	167203	83333	2	0.300	A	A	A	A
WAV	167205	83334	2	0.300	A	A	A	A
WAV	167207	83335	2	0.300	A	A	A	A
WAV	167211	83337	2	0.300	A	A	A	A
WAV	167213	83338	2	0.300	A	A	A	A
WAV	167215	83339	2	0.300	A	A	A	A
WAV	167219	83341	2	0.300	A	A	A	A
WAV	167221	83342	2	0.300	A	A	A	A
WAV	167223	83343	2	0.300	A	A	A	A
WAV	167227	83345	2	0.300	A	A	A	A
WAV	167229	83346	2	0.300	A	A	A	A
WAV	167231	83347	2	0.300	A	A	A	A
WAV	167233	83348	2	0.300	A	A	A	A
WAV	167235	83349	2	0.300	A	A	A	A
WAV	167237	83350	2	0.300	A	A	A	A
WAV	167239	83351	2	0.300	A	A	A	A
WAV	168363	83913	2	0.300	A	A	A	A
WAV	193911	96687	2	0.300	A	A	A	A