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 (
    103663, 103483, 103235, 103234, 103233, 
    103725, 103151, 103150, 103149, 103148, 
    103147, 103146, 103145, 103767, 103766, 
    103138, 103691, 103690, 103689, 103688
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00097

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	206812	103138	1	0.000	A	A	A	A
MP3	206826	103145	1	0.000	A	A	A	A
MP3	206828	103146	1	0.000	A	A	A	A
MP3	206830	103147	1	0.000	A	A	A	A
MP3	206832	103148	1	0.000	A	A	A	A
MP3	206834	103149	1	0.000	A	A	A	A
MP3	206836	103150	1	0.000	A	A	A	A
MP3	206838	103151	1	0.000	A	A	A	A
MP3	207002	103233	1	0.000	A	A	A	A
MP3	207004	103234	1	0.000	A	A	A	A
MP3	207006	103235	1	0.000	A	A	A	A
MP3	207502	103483	1	0.000	A	A	A	A
MP3	207862	103663	1	0.000	A	A	A	A
MP3	207912	103688	1	0.000	A	A	A	A
MP3	207914	103689	1	0.000	A	A	A	A
MP3	207916	103690	1	0.000	A	A	A	A
MP3	207918	103691	1	0.000	A	A	A	A
MP3	207986	103725	1	0.000	A	A	A	A
MP3	208068	103766	1	0.000	A	A	A	A
MP3	208070	103767	1	0.000	A	A	A	A
WAV	206813	103138	2	0.300	A	A	A	A
WAV	206827	103145	2	0.300	A	A	A	A
WAV	206829	103146	2	0.300	A	A	A	A
WAV	206831	103147	2	0.300	A	A	A	A
WAV	206833	103148	2	0.300	A	A	A	A
WAV	206835	103149	2	0.300	A	A	A	A
WAV	206837	103150	2	0.300	A	A	A	A
WAV	206839	103151	2	0.300	A	A	A	A
WAV	207003	103233	2	0.300	A	A	A	A
WAV	207005	103234	2	0.300	A	A	A	A
WAV	207007	103235	2	0.300	A	A	A	A
WAV	207503	103483	2	0.300	A	A	A	A
WAV	207863	103663	2	0.300	A	A	A	A
WAV	207913	103688	2	0.300	A	A	A	A
WAV	207915	103689	2	0.300	A	A	A	A
WAV	207917	103690	2	0.300	A	A	A	A
WAV	207919	103691	2	0.300	A	A	A	A
WAV	207987	103725	2	0.300	A	A	A	A
WAV	208069	103766	2	0.300	A	A	A	A
WAV	208071	103767	2	0.300	A	A	A	A