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 (
    95901, 95900, 95899, 95897, 95896, 95894, 
    95893, 95892, 95891, 95890, 95889, 
    95888, 95883, 95881, 95880, 95843, 
    96412, 96411, 96410, 96409
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00130

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	192222	95843	1	0.000	A	A	A	A
MP3	192296	95880	1	0.000	A	A	A	A
MP3	192298	95881	1	0.000	A	A	A	A
MP3	192302	95883	1	0.000	A	A	A	A
MP3	192312	95888	1	0.000	A	A	A	A
MP3	192314	95889	1	0.000	A	A	A	A
MP3	192316	95890	1	0.000	A	A	A	A
MP3	192318	95891	1	0.000	A	A	A	A
MP3	192320	95892	1	0.000	A	A	A	A
MP3	192322	95893	1	0.000	A	A	A	A
MP3	192324	95894	1	0.000	A	A	A	A
MP3	192328	95896	1	0.000	A	A	A	A
MP3	192330	95897	1	0.000	A	A	A	A
MP3	192334	95899	1	0.000	A	A	A	A
MP3	192336	95900	1	0.000	A	A	A	A
MP3	192338	95901	1	0.000	A	A	A	A
MP3	193354	96409	1	0.000	A	A	A	A
MP3	193356	96410	1	0.000	A	A	A	A
MP3	193358	96411	1	0.000	A	A	A	A
MP3	193360	96412	1	0.000	A	A	A	A
WAV	192223	95843	2	0.300	A	A	A	A
WAV	192297	95880	2	0.300	A	A	A	A
WAV	192299	95881	2	0.300	A	A	A	A
WAV	192303	95883	2	0.300	A	A	A	A
WAV	192313	95888	2	0.300	A	A	A	A
WAV	192315	95889	2	0.300	A	A	A	A
WAV	192317	95890	2	0.300	A	A	A	A
WAV	192319	95891	2	0.300	A	A	A	A
WAV	192321	95892	2	0.300	A	A	A	A
WAV	192323	95893	2	0.300	A	A	A	A
WAV	192325	95894	2	0.300	A	A	A	A
WAV	192329	95896	2	0.300	A	A	A	A
WAV	192331	95897	2	0.300	A	A	A	A
WAV	192335	95899	2	0.300	A	A	A	A
WAV	192337	95900	2	0.300	A	A	A	A
WAV	192339	95901	2	0.300	A	A	A	A
WAV	193355	96409	2	0.300	A	A	A	A
WAV	193357	96410	2	0.300	A	A	A	A
WAV	193359	96411	2	0.300	A	A	A	A
WAV	193361	96412	2	0.300	A	A	A	A