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 (
    103471, 104260, 104124, 104010, 103700, 
    103699, 103698, 103697, 103696, 103695, 
    103694, 103693, 103692, 103868, 103750, 
    103749, 103675, 104123, 103659, 103658
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00145

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	207478	103471	1	0.000	A	A	A	A
MP3	207852	103658	1	0.000	A	A	A	A
MP3	207854	103659	1	0.000	A	A	A	A
MP3	207886	103675	1	0.000	A	A	A	A
MP3	207920	103692	1	0.000	A	A	A	A
MP3	207922	103693	1	0.000	A	A	A	A
MP3	207924	103694	1	0.000	A	A	A	A
MP3	207926	103695	1	0.000	A	A	A	A
MP3	207928	103696	1	0.000	A	A	A	A
MP3	207930	103697	1	0.000	A	A	A	A
MP3	207932	103698	1	0.000	A	A	A	A
MP3	207934	103699	1	0.000	A	A	A	A
MP3	207936	103700	1	0.000	A	A	A	A
MP3	208034	103749	1	0.000	A	A	A	A
MP3	208036	103750	1	0.000	A	A	A	A
MP3	208272	103868	1	0.000	A	A	A	A
MP3	208556	104010	1	0.000	A	A	A	A
MP3	208782	104123	1	0.000	A	A	A	A
MP3	208784	104124	1	0.000	A	A	A	A
MP3	209056	104260	1	0.000	A	A	A	A
WAV	207479	103471	2	0.300	A	A	A	A
WAV	207853	103658	2	0.300	A	A	A	A
WAV	207855	103659	2	0.300	A	A	A	A
WAV	207887	103675	2	0.300	A	A	A	A
WAV	207921	103692	2	0.300	A	A	A	A
WAV	207923	103693	2	0.300	A	A	A	A
WAV	207925	103694	2	0.300	A	A	A	A
WAV	207927	103695	2	0.300	A	A	A	A
WAV	207929	103696	2	0.300	A	A	A	A
WAV	207931	103697	2	0.300	A	A	A	A
WAV	207933	103698	2	0.300	A	A	A	A
WAV	207935	103699	2	0.300	A	A	A	A
WAV	207937	103700	2	0.300	A	A	A	A
WAV	208035	103749	2	0.300	A	A	A	A
WAV	208037	103750	2	0.300	A	A	A	A
WAV	208273	103868	2	0.300	A	A	A	A
WAV	208557	104010	2	0.300	A	A	A	A
WAV	208783	104123	2	0.300	A	A	A	A
WAV	208785	104124	2	0.300	A	A	A	A
WAV	209057	104260	2	0.300	A	A	A	A