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 (
    104101, 104099, 104408, 104043, 104355, 
    104119, 104040, 104038, 104036, 104035, 
    104034, 104033, 104133, 104094, 104008, 
    104007, 103932, 103931, 103929, 103928
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00071

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	208392	103928	1	0.000	A	A	A	A
MP3	208394	103929	1	0.000	A	A	A	A
MP3	208398	103931	1	0.000	A	A	A	A
MP3	208400	103932	1	0.000	A	A	A	A
MP3	208550	104007	1	0.000	A	A	A	A
MP3	208552	104008	1	0.000	A	A	A	A
MP3	208602	104033	1	0.000	A	A	A	A
MP3	208604	104034	1	0.000	A	A	A	A
MP3	208606	104035	1	0.000	A	A	A	A
MP3	208608	104036	1	0.000	A	A	A	A
MP3	208612	104038	1	0.000	A	A	A	A
MP3	208616	104040	1	0.000	A	A	A	A
MP3	208622	104043	1	0.000	A	A	A	A
MP3	208724	104094	1	0.000	A	A	A	A
MP3	208734	104099	1	0.000	A	A	A	A
MP3	208738	104101	1	0.000	A	A	A	A
MP3	208774	104119	1	0.000	A	A	A	A
MP3	208802	104133	1	0.000	A	A	A	A
MP3	209246	104355	1	0.000	A	A	A	A
MP3	209352	104408	1	0.000	A	A	A	A
WAV	208393	103928	2	0.300	A	A	A	A
WAV	208395	103929	2	0.300	A	A	A	A
WAV	208399	103931	2	0.300	A	A	A	A
WAV	208401	103932	2	0.300	A	A	A	A
WAV	208551	104007	2	0.300	A	A	A	A
WAV	208553	104008	2	0.300	A	A	A	A
WAV	208603	104033	2	0.300	A	A	A	A
WAV	208605	104034	2	0.300	A	A	A	A
WAV	208607	104035	2	0.300	A	A	A	A
WAV	208609	104036	2	0.300	A	A	A	A
WAV	208613	104038	2	0.300	A	A	A	A
WAV	208617	104040	2	0.300	A	A	A	A
WAV	208623	104043	2	0.300	A	A	A	A
WAV	208725	104094	2	0.300	A	A	A	A
WAV	208735	104099	2	0.300	A	A	A	A
WAV	208739	104101	2	0.300	A	A	A	A
WAV	208775	104119	2	0.300	A	A	A	A
WAV	208803	104133	2	0.300	A	A	A	A
WAV	209247	104355	2	0.300	A	A	A	A
WAV	209353	104408	2	0.300	A	A	A	A