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 (
    103478, 103228, 103227, 103169, 103168, 
    102815, 102814, 102813, 102806, 102802, 
    102801, 103591, 102794, 102793, 102785, 
    102784, 102783, 102782, 103361, 103242
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00096

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	206100	102782	1	0.000	A	A	A	A
MP3	206102	102783	1	0.000	A	A	A	A
MP3	206104	102784	1	0.000	A	A	A	A
MP3	206106	102785	1	0.000	A	A	A	A
MP3	206122	102793	1	0.000	A	A	A	A
MP3	206124	102794	1	0.000	A	A	A	A
MP3	206138	102801	1	0.000	A	A	A	A
MP3	206140	102802	1	0.000	A	A	A	A
MP3	206148	102806	1	0.000	A	A	A	A
MP3	206162	102813	1	0.000	A	A	A	A
MP3	206164	102814	1	0.000	A	A	A	A
MP3	206166	102815	1	0.000	A	A	A	A
MP3	206872	103168	1	0.000	A	A	A	A
MP3	206874	103169	1	0.000	A	A	A	A
MP3	206990	103227	1	0.000	A	A	A	A
MP3	206992	103228	1	0.000	A	A	A	A
MP3	207020	103242	1	0.000	A	A	A	A
MP3	207258	103361	1	0.000	A	A	A	A
MP3	207492	103478	1	0.000	A	A	A	A
MP3	207718	103591	1	0.000	A	A	A	A
WAV	206101	102782	2	0.300	A	A	A	A
WAV	206103	102783	2	0.300	A	A	A	A
WAV	206105	102784	2	0.300	A	A	A	A
WAV	206107	102785	2	0.300	A	A	A	A
WAV	206123	102793	2	0.300	A	A	A	A
WAV	206125	102794	2	0.300	A	A	A	A
WAV	206139	102801	2	0.300	A	A	A	A
WAV	206141	102802	2	0.300	A	A	A	A
WAV	206149	102806	2	0.300	A	A	A	A
WAV	206163	102813	2	0.300	A	A	A	A
WAV	206165	102814	2	0.300	A	A	A	A
WAV	206167	102815	2	0.300	A	A	A	A
WAV	206873	103168	2	0.300	A	A	A	A
WAV	206875	103169	2	0.300	A	A	A	A
WAV	206991	103227	2	0.300	A	A	A	A
WAV	206993	103228	2	0.300	A	A	A	A
WAV	207021	103242	2	0.300	A	A	A	A
WAV	207259	103361	2	0.300	A	A	A	A
WAV	207493	103478	2	0.300	A	A	A	A
WAV	207719	103591	2	0.300	A	A	A	A