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 (
    2002, 2060, 2045, 2018, 2003, 2578, 2526, 
    79818, 79817, 73765, 73764, 2259, 73160, 
    2270, 2425, 2551, 2494, 2493, 2490, 
    2489, 2488, 2487, 2486, 2311, 2545, 
    2543, 2354, 72560, 2681, 21857, 2536, 
    2666, 2548, 97668
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00172

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		68	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	4003	2002	1	0.000	A	A	A	A
MP3	4005	2003	1	0.000	A	A	A	A
MP3	4035	2018	1	0.000	A	A	A	A
MP3	4089	2045	1	0.000	A	A	A	A
MP3	4119	2060	1	0.000	A	A	A	A
MP3	4517	2259	1	0.000	A	A	A	A
MP3	4539	2270	1	0.000	A	A	A	A
MP3	4621	2311	1	0.000	A	A	A	A
MP3	4707	2354	1	0.000	A	A	A	A
MP3	4849	2425	1	0.000	A	A	A	A
MP3	4971	2486	1	0.000	A	A	A	A
MP3	4973	2487	1	0.000	A	A	A	A
MP3	4975	2488	1	0.000	A	A	A	A
MP3	4977	2489	1	0.000	A	A	A	A
MP3	4979	2490	1	0.000	A	A	A	A
MP3	4985	2493	1	0.000	A	A	A	A
MP3	4987	2494	1	0.000	A	A	A	A
MP3	5051	2526	1	0.000	A	A	A	A
MP3	5071	2536	1	0.000	A	A	A	A
MP3	5085	2543	1	0.000	A	A	A	A
MP3	5089	2545	1	0.000	A	A	A	A
MP3	5095	2548	1	0.000	A	A	A	A
MP3	5101	2551	1	0.000	A	A	A	A
MP3	5155	2578	1	0.000	A	A	A	A
MP3	5331	2666	1	0.000	A	A	A	A
MP3	5361	2681	1	0.000	A	A	A	A
MP3	43713	21857	1	0.000	A	A	A	A
MP3	145634	72560	1	0.000	A	A	A	A
MP3	146834	73160	1	0.000	A	A	A	A
MP3	148042	73764	1	0.000	A	A	A	A
MP3	148044	73765	1	0.000	A	A	A	A
MP3	160170	79817	1	0.000	A	A	A	A
MP3	160172	79818	1	0.000	A	A	A	A
MP3	195872	97668	1	0.000	A	A	A	A
WAV	4004	2002	2	0.300	A	A	A	A
WAV	4006	2003	2	0.300	A	A	A	A
WAV	4036	2018	2	0.300	A	A	A	A
WAV	4090	2045	2	0.300	A	A	A	A
WAV	4120	2060	2	0.300	A	A	A	A
WAV	4518	2259	2	0.300	A	A	A	A
WAV	4540	2270	2	0.300	A	A	A	A
WAV	4622	2311	2	0.300	A	A	A	A
WAV	4708	2354	2	0.300	A	A	A	A
WAV	4850	2425	2	0.300	A	A	A	A
WAV	4972	2486	2	0.300	A	A	A	A
WAV	4974	2487	2	0.300	A	A	A	A
WAV	4976	2488	2	0.300	A	A	A	A
WAV	4978	2489	2	0.300	A	A	A	A
WAV	4980	2490	2	0.300	A	A	A	A
WAV	4986	2493	2	0.300	A	A	A	A
WAV	4988	2494	2	0.300	A	A	A	A
WAV	5052	2526	2	0.300	A	A	A	A
WAV	5072	2536	2	0.300	A	A	A	A
WAV	5086	2543	2	0.300	A	A	A	A
WAV	5090	2545	2	0.300	A	A	A	A
WAV	5096	2548	2	0.300	A	A	A	A
WAV	5102	2551	2	0.300	A	A	A	A
WAV	5156	2578	2	0.300	A	A	A	A
WAV	5332	2666	2	0.300	A	A	A	A
WAV	5362	2681	2	0.300	A	A	A	A
WAV	43714	21857	2	0.300	A	A	A	A
WAV	145635	72560	2	0.300	A	A	A	A
WAV	146835	73160	2	0.300	A	A	A	A
WAV	148043	73764	2	0.300	A	A	A	A
WAV	148045	73765	2	0.300	A	A	A	A
WAV	160171	79817	2	0.300	A	A	A	A
WAV	160173	79818	2	0.300	A	A	A	A
WAV	195873	97668	2	0.300	A	A	A	A