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 (
    94550, 94549, 94548, 94547, 94545, 94544, 
    94529, 94512, 87972, 94507, 94506, 
    94505, 94477, 94476, 94471, 94407, 
    94387, 94378, 93999, 93989
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00119

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	176480	87972	1	0.000	A	A	A	A
MP3	188514	93989	1	0.000	A	A	A	A
MP3	188534	93999	1	0.000	A	A	A	A
MP3	189292	94378	1	0.000	A	A	A	A
MP3	189310	94387	1	0.000	A	A	A	A
MP3	189350	94407	1	0.000	A	A	A	A
MP3	189478	94471	1	0.000	A	A	A	A
MP3	189488	94476	1	0.000	A	A	A	A
MP3	189490	94477	1	0.000	A	A	A	A
MP3	189546	94505	1	0.000	A	A	A	A
MP3	189548	94506	1	0.000	A	A	A	A
MP3	189550	94507	1	0.000	A	A	A	A
MP3	189560	94512	1	0.000	A	A	A	A
MP3	189594	94529	1	0.000	A	A	A	A
MP3	189624	94544	1	0.000	A	A	A	A
MP3	189626	94545	1	0.000	A	A	A	A
MP3	189630	94547	1	0.000	A	A	A	A
MP3	189632	94548	1	0.000	A	A	A	A
MP3	189634	94549	1	0.000	A	A	A	A
MP3	189636	94550	1	0.000	A	A	A	A
WAV	176481	87972	2	0.300	A	A	A	A
WAV	188515	93989	2	0.300	A	A	A	A
WAV	188535	93999	2	0.300	A	A	A	A
WAV	189293	94378	2	0.300	A	A	A	A
WAV	189311	94387	2	0.300	A	A	A	A
WAV	189351	94407	2	0.300	A	A	A	A
WAV	189479	94471	2	0.300	A	A	A	A
WAV	189489	94476	2	0.300	A	A	A	A
WAV	189491	94477	2	0.300	A	A	A	A
WAV	189547	94505	2	0.300	A	A	A	A
WAV	189549	94506	2	0.300	A	A	A	A
WAV	189551	94507	2	0.300	A	A	A	A
WAV	189561	94512	2	0.300	A	A	A	A
WAV	189595	94529	2	0.300	A	A	A	A
WAV	189625	94544	2	0.300	A	A	A	A
WAV	189627	94545	2	0.300	A	A	A	A
WAV	189631	94547	2	0.300	A	A	A	A
WAV	189633	94548	2	0.300	A	A	A	A
WAV	189635	94549	2	0.300	A	A	A	A
WAV	189637	94550	2	0.300	A	A	A	A