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 (
    96302, 96301, 96300, 96299, 96298, 96297, 
    96296, 96295, 96294, 96293, 96292, 
    96291, 96290, 96289, 96288, 96287, 
    96286, 96285, 96313, 96312
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00109

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	193106	96285	1	0.000	A	A	A	A
MP3	193108	96286	1	0.000	A	A	A	A
MP3	193110	96287	1	0.000	A	A	A	A
MP3	193112	96288	1	0.000	A	A	A	A
MP3	193114	96289	1	0.000	A	A	A	A
MP3	193116	96290	1	0.000	A	A	A	A
MP3	193118	96291	1	0.000	A	A	A	A
MP3	193120	96292	1	0.000	A	A	A	A
MP3	193122	96293	1	0.000	A	A	A	A
MP3	193124	96294	1	0.000	A	A	A	A
MP3	193126	96295	1	0.000	A	A	A	A
MP3	193128	96296	1	0.000	A	A	A	A
MP3	193130	96297	1	0.000	A	A	A	A
MP3	193132	96298	1	0.000	A	A	A	A
MP3	193134	96299	1	0.000	A	A	A	A
MP3	193136	96300	1	0.000	A	A	A	A
MP3	193138	96301	1	0.000	A	A	A	A
MP3	193140	96302	1	0.000	A	A	A	A
MP3	193160	96312	1	0.000	A	A	A	A
MP3	193162	96313	1	0.000	A	A	A	A
WAV	193107	96285	2	0.300	A	A	A	A
WAV	193109	96286	2	0.300	A	A	A	A
WAV	193111	96287	2	0.300	A	A	A	A
WAV	193113	96288	2	0.300	A	A	A	A
WAV	193115	96289	2	0.300	A	A	A	A
WAV	193117	96290	2	0.300	A	A	A	A
WAV	193119	96291	2	0.300	A	A	A	A
WAV	193121	96292	2	0.300	A	A	A	A
WAV	193123	96293	2	0.300	A	A	A	A
WAV	193125	96294	2	0.300	A	A	A	A
WAV	193127	96295	2	0.300	A	A	A	A
WAV	193129	96296	2	0.300	A	A	A	A
WAV	193131	96297	2	0.300	A	A	A	A
WAV	193133	96298	2	0.300	A	A	A	A
WAV	193135	96299	2	0.300	A	A	A	A
WAV	193137	96300	2	0.300	A	A	A	A
WAV	193139	96301	2	0.300	A	A	A	A
WAV	193141	96302	2	0.300	A	A	A	A
WAV	193161	96312	2	0.300	A	A	A	A
WAV	193163	96313	2	0.300	A	A	A	A