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 (
    100319, 100318, 100317, 100316, 100315, 
    100314, 100747, 100363, 100362, 100627, 
    100446, 100445, 100290, 100313, 100262, 
    100261, 100260, 100259, 100258, 100257
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00116

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	201050	100257	1	0.000	A	A	A	A
MP3	201052	100258	1	0.000	A	A	A	A
MP3	201054	100259	1	0.000	A	A	A	A
MP3	201056	100260	1	0.000	A	A	A	A
MP3	201058	100261	1	0.000	A	A	A	A
MP3	201060	100262	1	0.000	A	A	A	A
MP3	201116	100290	1	0.000	A	A	A	A
MP3	201162	100313	1	0.000	A	A	A	A
MP3	201164	100314	1	0.000	A	A	A	A
MP3	201166	100315	1	0.000	A	A	A	A
MP3	201168	100316	1	0.000	A	A	A	A
MP3	201170	100317	1	0.000	A	A	A	A
MP3	201172	100318	1	0.000	A	A	A	A
MP3	201174	100319	1	0.000	A	A	A	A
MP3	201260	100362	1	0.000	A	A	A	A
MP3	201262	100363	1	0.000	A	A	A	A
MP3	201426	100445	1	0.000	A	A	A	A
MP3	201428	100446	1	0.000	A	A	A	A
MP3	201790	100627	1	0.000	A	A	A	A
MP3	202030	100747	1	0.000	A	A	A	A
WAV	201051	100257	2	0.300	A	A	A	A
WAV	201053	100258	2	0.300	A	A	A	A
WAV	201055	100259	2	0.300	A	A	A	A
WAV	201057	100260	2	0.300	A	A	A	A
WAV	201059	100261	2	0.300	A	A	A	A
WAV	201061	100262	2	0.300	A	A	A	A
WAV	201117	100290	2	0.300	A	A	A	A
WAV	201163	100313	2	0.300	A	A	A	A
WAV	201165	100314	2	0.300	A	A	A	A
WAV	201167	100315	2	0.300	A	A	A	A
WAV	201169	100316	2	0.300	A	A	A	A
WAV	201171	100317	2	0.300	A	A	A	A
WAV	201173	100318	2	0.300	A	A	A	A
WAV	201175	100319	2	0.300	A	A	A	A
WAV	201261	100362	2	0.300	A	A	A	A
WAV	201263	100363	2	0.300	A	A	A	A
WAV	201427	100445	2	0.300	A	A	A	A
WAV	201429	100446	2	0.300	A	A	A	A
WAV	201791	100627	2	0.300	A	A	A	A
WAV	202031	100747	2	0.300	A	A	A	A