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 (
    96092, 96091, 96090, 96089, 96088, 96087, 
    96086, 96085, 96084, 96083, 96082, 
    96081, 96080, 96079, 96078, 96077, 
    96076, 96075, 96074, 96073
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00101

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	192682	96073	1	0.000	A	A	A	A
MP3	192684	96074	1	0.000	A	A	A	A
MP3	192686	96075	1	0.000	A	A	A	A
MP3	192688	96076	1	0.000	A	A	A	A
MP3	192690	96077	1	0.000	A	A	A	A
MP3	192692	96078	1	0.000	A	A	A	A
MP3	192694	96079	1	0.000	A	A	A	A
MP3	192696	96080	1	0.000	A	A	A	A
MP3	192698	96081	1	0.000	A	A	A	A
MP3	192700	96082	1	0.000	A	A	A	A
MP3	192702	96083	1	0.000	A	A	A	A
MP3	192704	96084	1	0.000	A	A	A	A
MP3	192706	96085	1	0.000	A	A	A	A
MP3	192708	96086	1	0.000	A	A	A	A
MP3	192710	96087	1	0.000	A	A	A	A
MP3	192712	96088	1	0.000	A	A	A	A
MP3	192714	96089	1	0.000	A	A	A	A
MP3	192716	96090	1	0.000	A	A	A	A
MP3	192718	96091	1	0.000	A	A	A	A
MP3	192720	96092	1	0.000	A	A	A	A
WAV	192683	96073	2	0.300	A	A	A	A
WAV	192685	96074	2	0.300	A	A	A	A
WAV	192687	96075	2	0.300	A	A	A	A
WAV	192689	96076	2	0.300	A	A	A	A
WAV	192691	96077	2	0.300	A	A	A	A
WAV	192693	96078	2	0.300	A	A	A	A
WAV	192695	96079	2	0.300	A	A	A	A
WAV	192697	96080	2	0.300	A	A	A	A
WAV	192699	96081	2	0.300	A	A	A	A
WAV	192701	96082	2	0.300	A	A	A	A
WAV	192703	96083	2	0.300	A	A	A	A
WAV	192705	96084	2	0.300	A	A	A	A
WAV	192707	96085	2	0.300	A	A	A	A
WAV	192709	96086	2	0.300	A	A	A	A
WAV	192711	96087	2	0.300	A	A	A	A
WAV	192713	96088	2	0.300	A	A	A	A
WAV	192715	96089	2	0.300	A	A	A	A
WAV	192717	96090	2	0.300	A	A	A	A
WAV	192719	96091	2	0.300	A	A	A	A
WAV	192721	96092	2	0.300	A	A	A	A