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 (
    46230, 46229, 53370, 46596, 46228, 46216, 
    46214, 46213, 46189, 46188, 46215, 
    46205, 46204, 46203, 46202, 46201, 
    46200, 46199, 46198, 46197, 46196, 
    46195, 46194, 46193, 46192, 46191, 
    46156, 46155, 46154, 46153, 46185, 
    46138, 86228, 86227, 86226, 46058, 
    46051, 46043, 46042, 46041, 46040, 
    105329, 46015, 46011, 45993, 45987, 
    45986, 46071, 46070, 46069, 46068, 
    46067, 45964, 45962, 45942, 45941, 
    45940, 45939, 45938, 45937, 45928, 
    45847, 46098, 45840, 45835, 45763, 
    45762, 45744, 46171, 45828, 45712, 
    45626, 45556, 45555, 45528, 45527, 
    45526, 45525, 45524, 45523
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00191

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		160	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	91112	45523	1	0.000	A	A	A	A
MP3	91114	45524	1	0.000	A	A	A	A
MP3	91116	45525	1	0.000	A	A	A	A
MP3	91118	45526	1	0.000	A	A	A	A
MP3	91120	45527	1	0.000	A	A	A	A
MP3	91122	45528	1	0.000	A	A	A	A
MP3	91176	45555	1	0.000	A	A	A	A
MP3	91178	45556	1	0.000	A	A	A	A
MP3	91318	45626	1	0.000	A	A	A	A
MP3	91490	45712	1	0.000	A	A	A	A
MP3	91554	45744	1	0.000	A	A	A	A
MP3	91590	45762	1	0.000	A	A	A	A
MP3	91592	45763	1	0.000	A	A	A	A
MP3	91722	45828	1	0.000	A	A	A	A
MP3	91736	45835	1	0.000	A	A	A	A
MP3	91746	45840	1	0.000	A	A	A	A
MP3	91760	45847	1	0.000	A	A	A	A
MP3	91922	45928	1	0.000	A	A	A	A
MP3	91940	45937	1	0.000	A	A	A	A
MP3	91942	45938	1	0.000	A	A	A	A
MP3	91944	45939	1	0.000	A	A	A	A
MP3	91946	45940	1	0.000	A	A	A	A
MP3	91948	45941	1	0.000	A	A	A	A
MP3	91950	45942	1	0.000	A	A	A	A
MP3	91990	45962	1	0.000	A	A	A	A
MP3	91994	45964	1	0.000	A	A	A	A
MP3	92038	45986	1	0.000	A	A	A	A
MP3	92040	45987	1	0.000	A	A	A	A
MP3	92052	45993	1	0.000	A	A	A	A
MP3	92088	46011	1	0.000	A	A	A	A
MP3	92096	46015	1	0.000	A	A	A	A
MP3	92146	46040	1	0.000	A	A	A	A
MP3	92148	46041	1	0.000	A	A	A	A
MP3	92150	46042	1	0.000	A	A	A	A
MP3	92152	46043	1	0.000	A	A	A	A
MP3	92168	46051	1	0.000	A	A	A	A
MP3	92182	46058	1	0.000	A	A	A	A
MP3	92200	46067	1	0.000	A	A	A	A
MP3	92202	46068	1	0.000	A	A	A	A
MP3	92204	46069	1	0.000	A	A	A	A
MP3	92206	46070	1	0.000	A	A	A	A
MP3	92208	46071	1	0.000	A	A	A	A
MP3	92262	46098	1	0.000	A	A	A	A
MP3	92342	46138	1	0.000	A	A	A	A
MP3	92372	46153	1	0.000	A	A	A	A
MP3	92374	46154	1	0.000	A	A	A	A
MP3	92376	46155	1	0.000	A	A	A	A
MP3	92378	46156	1	0.000	A	A	A	A
MP3	92408	46171	1	0.000	A	A	A	A
MP3	92436	46185	1	0.000	A	A	A	A
MP3	92442	46188	1	0.000	A	A	A	A
MP3	92444	46189	1	0.000	A	A	A	A
MP3	92448	46191	1	0.000	A	A	A	A
MP3	92450	46192	1	0.000	A	A	A	A
MP3	92452	46193	1	0.000	A	A	A	A
MP3	92454	46194	1	0.000	A	A	A	A
MP3	92456	46195	1	0.000	A	A	A	A
MP3	92458	46196	1	0.000	A	A	A	A
MP3	92460	46197	1	0.000	A	A	A	A
MP3	92462	46198	1	0.000	A	A	A	A
MP3	92464	46199	1	0.000	A	A	A	A
MP3	92466	46200	1	0.000	A	A	A	A
MP3	92468	46201	1	0.000	A	A	A	A
MP3	92470	46202	1	0.000	A	A	A	A
MP3	92472	46203	1	0.000	A	A	A	A
MP3	92474	46204	1	0.000	A	A	A	A
MP3	92476	46205	1	0.000	A	A	A	A
MP3	92492	46213	1	0.000	A	A	A	A
MP3	92494	46214	1	0.000	A	A	A	A
MP3	92496	46215	1	0.000	A	A	A	A
MP3	92498	46216	1	0.000	A	A	A	A
MP3	92522	46228	1	0.000	A	A	A	A
MP3	92524	46229	1	0.000	A	A	A	A
MP3	92526	46230	1	0.000	A	A	A	A
MP3	93258	46596	1	0.000	A	A	A	A
MP3	106806	53370	1	0.000	A	A	A	A
MP3	172988	86226	1	0.000	A	A	A	A
MP3	172990	86227	1	0.000	A	A	A	A
MP3	172992	86228	1	0.000	A	A	A	A
MP3	211194	105329	1	0.000	A	A	A	A
WAV	91113	45523	2	0.300	A	A	A	A
WAV	91115	45524	2	0.300	A	A	A	A
WAV	91117	45525	2	0.300	A	A	A	A
WAV	91119	45526	2	0.300	A	A	A	A
WAV	91121	45527	2	0.300	A	A	A	A
WAV	91123	45528	2	0.300	A	A	A	A
WAV	91177	45555	2	0.300	A	A	A	A
WAV	91179	45556	2	0.300	A	A	A	A
WAV	91319	45626	2	0.300	A	A	A	A
WAV	91491	45712	2	0.300	A	A	A	A
WAV	91555	45744	2	0.300	A	A	A	A
WAV	91591	45762	2	0.300	A	A	A	A
WAV	91593	45763	2	0.300	A	A	A	A
WAV	91723	45828	2	0.300	A	A	A	A
WAV	91737	45835	2	0.300	A	A	A	A
WAV	91747	45840	2	0.300	A	A	A	A
WAV	91761	45847	2	0.300	A	A	A	A
WAV	91923	45928	2	0.300	A	A	A	A
WAV	91941	45937	2	0.300	A	A	A	A
WAV	91943	45938	2	0.300	A	A	A	A
WAV	91945	45939	2	0.300	A	A	A	A
WAV	91947	45940	2	0.300	A	A	A	A
WAV	91949	45941	2	0.300	A	A	A	A
WAV	91951	45942	2	0.300	A	A	A	A
WAV	91991	45962	2	0.300	A	A	A	A
WAV	91995	45964	2	0.300	A	A	A	A
WAV	92039	45986	2	0.300	A	A	A	A
WAV	92041	45987	2	0.300	A	A	A	A
WAV	92053	45993	2	0.300	A	A	A	A
WAV	92089	46011	2	0.300	A	A	A	A
WAV	92097	46015	2	0.300	A	A	A	A
WAV	92147	46040	2	0.300	A	A	A	A
WAV	92149	46041	2	0.300	A	A	A	A
WAV	92151	46042	2	0.300	A	A	A	A
WAV	92153	46043	2	0.300	A	A	A	A
WAV	92169	46051	2	0.300	A	A	A	A
WAV	92183	46058	2	0.300	A	A	A	A
WAV	92201	46067	2	0.300	A	A	A	A
WAV	92203	46068	2	0.300	A	A	A	A
WAV	92205	46069	2	0.300	A	A	A	A
WAV	92207	46070	2	0.300	A	A	A	A
WAV	92209	46071	2	0.300	A	A	A	A
WAV	92263	46098	2	0.300	A	A	A	A
WAV	92343	46138	2	0.300	A	A	A	A
WAV	92373	46153	2	0.300	A	A	A	A
WAV	92375	46154	2	0.300	A	A	A	A
WAV	92377	46155	2	0.300	A	A	A	A
WAV	92379	46156	2	0.300	A	A	A	A
WAV	92409	46171	2	0.300	A	A	A	A
WAV	92437	46185	2	0.300	A	A	A	A
WAV	92443	46188	2	0.300	A	A	A	A
WAV	92445	46189	2	0.300	A	A	A	A
WAV	92449	46191	2	0.300	A	A	A	A
WAV	92451	46192	2	0.300	A	A	A	A
WAV	92453	46193	2	0.300	A	A	A	A
WAV	92455	46194	2	0.300	A	A	A	A
WAV	92457	46195	2	0.300	A	A	A	A
WAV	92459	46196	2	0.300	A	A	A	A
WAV	92461	46197	2	0.300	A	A	A	A
WAV	92463	46198	2	0.300	A	A	A	A
WAV	92465	46199	2	0.300	A	A	A	A
WAV	92467	46200	2	0.300	A	A	A	A
WAV	92469	46201	2	0.300	A	A	A	A
WAV	92471	46202	2	0.300	A	A	A	A
WAV	92473	46203	2	0.300	A	A	A	A
WAV	92475	46204	2	0.300	A	A	A	A
WAV	92477	46205	2	0.300	A	A	A	A
WAV	92493	46213	2	0.300	A	A	A	A
WAV	92495	46214	2	0.300	A	A	A	A
WAV	92497	46215	2	0.300	A	A	A	A
WAV	92499	46216	2	0.300	A	A	A	A
WAV	92523	46228	2	0.300	A	A	A	A
WAV	92525	46229	2	0.300	A	A	A	A
WAV	92527	46230	2	0.300	A	A	A	A
WAV	93259	46596	2	0.300	A	A	A	A
WAV	106807	53370	2	0.300	A	A	A	A
WAV	172989	86226	2	0.300	A	A	A	A
WAV	172991	86227	2	0.300	A	A	A	A
WAV	172993	86228	2	0.300	A	A	A	A
WAV	211195	105329	2	0.300	A	A	A	A