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 (
    90048, 90047, 90046, 90045, 90044, 90043, 
    90042, 90041, 90040, 90039, 90038, 
    90037, 90036, 90035, 90034, 90033, 
    90032, 90031, 90030, 90029, 90028, 
    90027, 90026, 90025, 90024, 89378, 
    89901, 89161, 89160, 89159, 89158, 
    89157, 89156, 89155, 89154, 89141, 
    89112, 89111, 89110
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00155

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		78	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	178756	89110	1	0.000	A	A	A	A
MP3	178758	89111	1	0.000	A	A	A	A
MP3	178760	89112	1	0.000	A	A	A	A
MP3	178818	89141	1	0.000	A	A	A	A
MP3	178844	89154	1	0.000	A	A	A	A
MP3	178846	89155	1	0.000	A	A	A	A
MP3	178848	89156	1	0.000	A	A	A	A
MP3	178850	89157	1	0.000	A	A	A	A
MP3	178852	89158	1	0.000	A	A	A	A
MP3	178854	89159	1	0.000	A	A	A	A
MP3	178856	89160	1	0.000	A	A	A	A
MP3	178858	89161	1	0.000	A	A	A	A
MP3	179292	89378	1	0.000	A	A	A	A
MP3	180338	89901	1	0.000	A	A	A	A
MP3	180584	90024	1	0.000	A	A	A	A
MP3	180586	90025	1	0.000	A	A	A	A
MP3	180588	90026	1	0.000	A	A	A	A
MP3	180590	90027	1	0.000	A	A	A	A
MP3	180592	90028	1	0.000	A	A	A	A
MP3	180594	90029	1	0.000	A	A	A	A
MP3	180596	90030	1	0.000	A	A	A	A
MP3	180598	90031	1	0.000	A	A	A	A
MP3	180600	90032	1	0.000	A	A	A	A
MP3	180602	90033	1	0.000	A	A	A	A
MP3	180604	90034	1	0.000	A	A	A	A
MP3	180606	90035	1	0.000	A	A	A	A
MP3	180608	90036	1	0.000	A	A	A	A
MP3	180610	90037	1	0.000	A	A	A	A
MP3	180612	90038	1	0.000	A	A	A	A
MP3	180614	90039	1	0.000	A	A	A	A
MP3	180616	90040	1	0.000	A	A	A	A
MP3	180618	90041	1	0.000	A	A	A	A
MP3	180620	90042	1	0.000	A	A	A	A
MP3	180622	90043	1	0.000	A	A	A	A
MP3	180624	90044	1	0.000	A	A	A	A
MP3	180626	90045	1	0.000	A	A	A	A
MP3	180628	90046	1	0.000	A	A	A	A
MP3	180630	90047	1	0.000	A	A	A	A
MP3	180632	90048	1	0.000	A	A	A	A
WAV	178757	89110	2	0.300	A	A	A	A
WAV	178759	89111	2	0.300	A	A	A	A
WAV	178761	89112	2	0.300	A	A	A	A
WAV	178819	89141	2	0.300	A	A	A	A
WAV	178845	89154	2	0.300	A	A	A	A
WAV	178847	89155	2	0.300	A	A	A	A
WAV	178849	89156	2	0.300	A	A	A	A
WAV	178851	89157	2	0.300	A	A	A	A
WAV	178853	89158	2	0.300	A	A	A	A
WAV	178855	89159	2	0.300	A	A	A	A
WAV	178857	89160	2	0.300	A	A	A	A
WAV	178859	89161	2	0.300	A	A	A	A
WAV	179293	89378	2	0.300	A	A	A	A
WAV	180339	89901	2	0.300	A	A	A	A
WAV	180585	90024	2	0.300	A	A	A	A
WAV	180587	90025	2	0.300	A	A	A	A
WAV	180589	90026	2	0.300	A	A	A	A
WAV	180591	90027	2	0.300	A	A	A	A
WAV	180593	90028	2	0.300	A	A	A	A
WAV	180595	90029	2	0.300	A	A	A	A
WAV	180597	90030	2	0.300	A	A	A	A
WAV	180599	90031	2	0.300	A	A	A	A
WAV	180601	90032	2	0.300	A	A	A	A
WAV	180603	90033	2	0.300	A	A	A	A
WAV	180605	90034	2	0.300	A	A	A	A
WAV	180607	90035	2	0.300	A	A	A	A
WAV	180609	90036	2	0.300	A	A	A	A
WAV	180611	90037	2	0.300	A	A	A	A
WAV	180613	90038	2	0.300	A	A	A	A
WAV	180615	90039	2	0.300	A	A	A	A
WAV	180617	90040	2	0.300	A	A	A	A
WAV	180619	90041	2	0.300	A	A	A	A
WAV	180621	90042	2	0.300	A	A	A	A
WAV	180623	90043	2	0.300	A	A	A	A
WAV	180625	90044	2	0.300	A	A	A	A
WAV	180627	90045	2	0.300	A	A	A	A
WAV	180629	90046	2	0.300	A	A	A	A
WAV	180631	90047	2	0.300	A	A	A	A
WAV	180633	90048	2	0.300	A	A	A	A