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 (
    100199, 100198, 100197, 100196, 100195, 
    100194, 100193, 100192, 100191, 100190, 
    100189, 100188, 100186, 100133, 100132, 
    100131, 100583, 100582, 100581, 100119
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00087

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	200774	100119	1	0.000	A	A	A	A
MP3	200798	100131	1	0.000	A	A	A	A
MP3	200800	100132	1	0.000	A	A	A	A
MP3	200802	100133	1	0.000	A	A	A	A
MP3	200908	100186	1	0.000	A	A	A	A
MP3	200912	100188	1	0.000	A	A	A	A
MP3	200914	100189	1	0.000	A	A	A	A
MP3	200916	100190	1	0.000	A	A	A	A
MP3	200918	100191	1	0.000	A	A	A	A
MP3	200920	100192	1	0.000	A	A	A	A
MP3	200922	100193	1	0.000	A	A	A	A
MP3	200924	100194	1	0.000	A	A	A	A
MP3	200926	100195	1	0.000	A	A	A	A
MP3	200928	100196	1	0.000	A	A	A	A
MP3	200930	100197	1	0.000	A	A	A	A
MP3	200932	100198	1	0.000	A	A	A	A
MP3	200934	100199	1	0.000	A	A	A	A
MP3	201698	100581	1	0.000	A	A	A	A
MP3	201700	100582	1	0.000	A	A	A	A
MP3	201702	100583	1	0.000	A	A	A	A
WAV	200775	100119	2	0.300	A	A	A	A
WAV	200799	100131	2	0.300	A	A	A	A
WAV	200801	100132	2	0.300	A	A	A	A
WAV	200803	100133	2	0.300	A	A	A	A
WAV	200909	100186	2	0.300	A	A	A	A
WAV	200913	100188	2	0.300	A	A	A	A
WAV	200915	100189	2	0.300	A	A	A	A
WAV	200917	100190	2	0.300	A	A	A	A
WAV	200919	100191	2	0.300	A	A	A	A
WAV	200921	100192	2	0.300	A	A	A	A
WAV	200923	100193	2	0.300	A	A	A	A
WAV	200925	100194	2	0.300	A	A	A	A
WAV	200927	100195	2	0.300	A	A	A	A
WAV	200929	100196	2	0.300	A	A	A	A
WAV	200931	100197	2	0.300	A	A	A	A
WAV	200933	100198	2	0.300	A	A	A	A
WAV	200935	100199	2	0.300	A	A	A	A
WAV	201699	100581	2	0.300	A	A	A	A
WAV	201701	100582	2	0.300	A	A	A	A
WAV	201703	100583	2	0.300	A	A	A	A