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 (
    94610, 94609, 94608, 94607, 94606, 94605, 
    94604, 94603, 94602, 94601, 94600, 
    94599, 94598, 94597, 94504, 94503, 
    94502, 94103, 94087, 94086
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00138

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	188708	94086	1	0.000	A	A	A	A
MP3	188710	94087	1	0.000	A	A	A	A
MP3	188742	94103	1	0.000	A	A	A	A
MP3	189540	94502	1	0.000	A	A	A	A
MP3	189542	94503	1	0.000	A	A	A	A
MP3	189544	94504	1	0.000	A	A	A	A
MP3	189730	94597	1	0.000	A	A	A	A
MP3	189732	94598	1	0.000	A	A	A	A
MP3	189734	94599	1	0.000	A	A	A	A
MP3	189736	94600	1	0.000	A	A	A	A
MP3	189738	94601	1	0.000	A	A	A	A
MP3	189740	94602	1	0.000	A	A	A	A
MP3	189742	94603	1	0.000	A	A	A	A
MP3	189744	94604	1	0.000	A	A	A	A
MP3	189746	94605	1	0.000	A	A	A	A
MP3	189748	94606	1	0.000	A	A	A	A
MP3	189750	94607	1	0.000	A	A	A	A
MP3	189752	94608	1	0.000	A	A	A	A
MP3	189754	94609	1	0.000	A	A	A	A
MP3	189756	94610	1	0.000	A	A	A	A
WAV	188709	94086	2	0.300	A	A	A	A
WAV	188711	94087	2	0.300	A	A	A	A
WAV	188743	94103	2	0.300	A	A	A	A
WAV	189541	94502	2	0.300	A	A	A	A
WAV	189543	94503	2	0.300	A	A	A	A
WAV	189545	94504	2	0.300	A	A	A	A
WAV	189731	94597	2	0.300	A	A	A	A
WAV	189733	94598	2	0.300	A	A	A	A
WAV	189735	94599	2	0.300	A	A	A	A
WAV	189737	94600	2	0.300	A	A	A	A
WAV	189739	94601	2	0.300	A	A	A	A
WAV	189741	94602	2	0.300	A	A	A	A
WAV	189743	94603	2	0.300	A	A	A	A
WAV	189745	94604	2	0.300	A	A	A	A
WAV	189747	94605	2	0.300	A	A	A	A
WAV	189749	94606	2	0.300	A	A	A	A
WAV	189751	94607	2	0.300	A	A	A	A
WAV	189753	94608	2	0.300	A	A	A	A
WAV	189755	94609	2	0.300	A	A	A	A
WAV	189757	94610	2	0.300	A	A	A	A