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 (
    94894, 94826, 94825, 94809, 94588, 94585, 
    94518, 93786, 93729, 87973, 94802, 
    94779, 94739, 94738, 94737, 94727, 
    94726, 94725, 94636, 94584
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00098

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	176482	87973	1	0.000	A	A	A	A
MP3	187994	93729	1	0.000	A	A	A	A
MP3	188108	93786	1	0.000	A	A	A	A
MP3	189572	94518	1	0.000	A	A	A	A
MP3	189704	94584	1	0.000	A	A	A	A
MP3	189706	94585	1	0.000	A	A	A	A
MP3	189712	94588	1	0.000	A	A	A	A
MP3	189808	94636	1	0.000	A	A	A	A
MP3	189986	94725	1	0.000	A	A	A	A
MP3	189988	94726	1	0.000	A	A	A	A
MP3	189990	94727	1	0.000	A	A	A	A
MP3	190010	94737	1	0.000	A	A	A	A
MP3	190012	94738	1	0.000	A	A	A	A
MP3	190014	94739	1	0.000	A	A	A	A
MP3	190094	94779	1	0.000	A	A	A	A
MP3	190140	94802	1	0.000	A	A	A	A
MP3	190154	94809	1	0.000	A	A	A	A
MP3	190186	94825	1	0.000	A	A	A	A
MP3	190188	94826	1	0.000	A	A	A	A
MP3	190324	94894	1	0.000	A	A	A	A
WAV	176483	87973	2	0.300	A	A	A	A
WAV	187995	93729	2	0.300	A	A	A	A
WAV	188109	93786	2	0.300	A	A	A	A
WAV	189573	94518	2	0.300	A	A	A	A
WAV	189705	94584	2	0.300	A	A	A	A
WAV	189707	94585	2	0.300	A	A	A	A
WAV	189713	94588	2	0.300	A	A	A	A
WAV	189809	94636	2	0.300	A	A	A	A
WAV	189987	94725	2	0.300	A	A	A	A
WAV	189989	94726	2	0.300	A	A	A	A
WAV	189991	94727	2	0.300	A	A	A	A
WAV	190011	94737	2	0.300	A	A	A	A
WAV	190013	94738	2	0.300	A	A	A	A
WAV	190015	94739	2	0.300	A	A	A	A
WAV	190095	94779	2	0.300	A	A	A	A
WAV	190141	94802	2	0.300	A	A	A	A
WAV	190155	94809	2	0.300	A	A	A	A
WAV	190187	94825	2	0.300	A	A	A	A
WAV	190189	94826	2	0.300	A	A	A	A
WAV	190325	94894	2	0.300	A	A	A	A