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 (
    42906, 42905, 42904, 42903, 42902, 42901, 
    42900, 42899, 42898, 42897, 42896, 
    42895, 42894, 42893, 42892, 42891, 
    42890, 42889, 42888, 42887, 42886, 
    42885, 42884, 42883, 65958, 65957, 
    65956, 65955, 42591, 42573, 42572, 
    42571, 42417, 42565, 42435, 42434, 
    42409, 42436, 42568, 42412, 42408, 
    42407, 42372, 42285, 85537, 85536, 
    85535, 85534, 85533, 85532, 85531, 
    42369, 42556, 42394, 42374, 42373, 
    42367, 42252, 42377, 42338, 42337, 
    42336, 42335, 42323, 42322, 42321, 
    42320, 42203, 42305, 42304, 42303, 
    42307, 42170, 42169, 42168, 42167, 
    42166, 42165, 42164, 42163
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00284

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	84392	42163	1	0.000	A	A	A	A
MP3	84394	42164	1	0.000	A	A	A	A
MP3	84396	42165	1	0.000	A	A	A	A
MP3	84398	42166	1	0.000	A	A	A	A
MP3	84400	42167	1	0.000	A	A	A	A
MP3	84402	42168	1	0.000	A	A	A	A
MP3	84404	42169	1	0.000	A	A	A	A
MP3	84406	42170	1	0.000	A	A	A	A
MP3	84472	42203	1	0.000	A	A	A	A
MP3	84570	42252	1	0.000	A	A	A	A
MP3	84636	42285	1	0.000	A	A	A	A
MP3	84672	42303	1	0.000	A	A	A	A
MP3	84674	42304	1	0.000	A	A	A	A
MP3	84676	42305	1	0.000	A	A	A	A
MP3	84680	42307	1	0.000	A	A	A	A
MP3	84706	42320	1	0.000	A	A	A	A
MP3	84708	42321	1	0.000	A	A	A	A
MP3	84710	42322	1	0.000	A	A	A	A
MP3	84712	42323	1	0.000	A	A	A	A
MP3	84736	42335	1	0.000	A	A	A	A
MP3	84738	42336	1	0.000	A	A	A	A
MP3	84740	42337	1	0.000	A	A	A	A
MP3	84742	42338	1	0.000	A	A	A	A
MP3	84800	42367	1	0.000	A	A	A	A
MP3	84804	42369	1	0.000	A	A	A	A
MP3	84810	42372	1	0.000	A	A	A	A
MP3	84812	42373	1	0.000	A	A	A	A
MP3	84814	42374	1	0.000	A	A	A	A
MP3	84820	42377	1	0.000	A	A	A	A
MP3	84854	42394	1	0.000	A	A	A	A
MP3	84880	42407	1	0.000	A	A	A	A
MP3	84882	42408	1	0.000	A	A	A	A
MP3	84884	42409	1	0.000	A	A	A	A
MP3	84890	42412	1	0.000	A	A	A	A
MP3	84900	42417	1	0.000	A	A	A	A
MP3	84934	42434	1	0.000	A	A	A	A
MP3	84936	42435	1	0.000	A	A	A	A
MP3	84938	42436	1	0.000	A	A	A	A
MP3	85178	42556	1	0.000	A	A	A	A
MP3	85196	42565	1	0.000	A	A	A	A
MP3	85202	42568	1	0.000	A	A	A	A
MP3	85208	42571	1	0.000	A	A	A	A
MP3	85210	42572	1	0.000	A	A	A	A
MP3	85212	42573	1	0.000	A	A	A	A
MP3	85248	42591	1	0.000	A	A	A	A
MP3	85832	42883	1	0.000	A	A	A	A
MP3	85834	42884	1	0.000	A	A	A	A
MP3	85836	42885	1	0.000	A	A	A	A
MP3	85838	42886	1	0.000	A	A	A	A
MP3	85840	42887	1	0.000	A	A	A	A
MP3	85842	42888	1	0.000	A	A	A	A
MP3	85844	42889	1	0.000	A	A	A	A
MP3	85846	42890	1	0.000	A	A	A	A
MP3	85848	42891	1	0.000	A	A	A	A
MP3	85850	42892	1	0.000	A	A	A	A
MP3	85852	42893	1	0.000	A	A	A	A
MP3	85854	42894	1	0.000	A	A	A	A
MP3	85856	42895	1	0.000	A	A	A	A
MP3	85858	42896	1	0.000	A	A	A	A
MP3	85860	42897	1	0.000	A	A	A	A
MP3	85862	42898	1	0.000	A	A	A	A
MP3	85864	42899	1	0.000	A	A	A	A
MP3	85866	42900	1	0.000	A	A	A	A
MP3	85868	42901	1	0.000	A	A	A	A
MP3	85870	42902	1	0.000	A	A	A	A
MP3	85872	42903	1	0.000	A	A	A	A
MP3	85874	42904	1	0.000	A	A	A	A
MP3	85876	42905	1	0.000	A	A	A	A
MP3	85878	42906	1	0.000	A	A	A	A
MP3	132368	65955	1	0.000	A	A	A	A
MP3	132370	65956	1	0.000	A	A	A	A
MP3	132372	65957	1	0.000	A	A	A	A
MP3	132374	65958	1	0.000	A	A	A	A
MP3	171598	85531	1	0.000	A	A	A	A
MP3	171600	85532	1	0.000	A	A	A	A
MP3	171602	85533	1	0.000	A	A	A	A
MP3	171604	85534	1	0.000	A	A	A	A
MP3	171606	85535	1	0.000	A	A	A	A
MP3	171608	85536	1	0.000	A	A	A	A
MP3	171610	85537	1	0.000	A	A	A	A
WAV	84393	42163	2	0.300	A	A	A	A
WAV	84395	42164	2	0.300	A	A	A	A
WAV	84397	42165	2	0.300	A	A	A	A
WAV	84399	42166	2	0.300	A	A	A	A
WAV	84401	42167	2	0.300	A	A	A	A
WAV	84403	42168	2	0.300	A	A	A	A
WAV	84405	42169	2	0.300	A	A	A	A
WAV	84407	42170	2	0.300	A	A	A	A
WAV	84473	42203	2	0.300	A	A	A	A
WAV	84571	42252	2	0.300	A	A	A	A
WAV	84637	42285	2	0.300	A	A	A	A
WAV	84673	42303	2	0.300	A	A	A	A
WAV	84675	42304	2	0.300	A	A	A	A
WAV	84677	42305	2	0.300	A	A	A	A
WAV	84681	42307	2	0.300	A	A	A	A
WAV	84707	42320	2	0.300	A	A	A	A
WAV	84709	42321	2	0.300	A	A	A	A
WAV	84711	42322	2	0.300	A	A	A	A
WAV	84713	42323	2	0.300	A	A	A	A
WAV	84737	42335	2	0.300	A	A	A	A
WAV	84739	42336	2	0.300	A	A	A	A
WAV	84741	42337	2	0.300	A	A	A	A
WAV	84743	42338	2	0.300	A	A	A	A
WAV	84801	42367	2	0.300	A	A	A	A
WAV	84805	42369	2	0.300	A	A	A	A
WAV	84811	42372	2	0.300	A	A	A	A
WAV	84813	42373	2	0.300	A	A	A	A
WAV	84815	42374	2	0.300	A	A	A	A
WAV	84821	42377	2	0.300	A	A	A	A
WAV	84855	42394	2	0.300	A	A	A	A
WAV	84881	42407	2	0.300	A	A	A	A
WAV	84883	42408	2	0.300	A	A	A	A
WAV	84885	42409	2	0.300	A	A	A	A
WAV	84891	42412	2	0.300	A	A	A	A
WAV	84901	42417	2	0.300	A	A	A	A
WAV	84935	42434	2	0.300	A	A	A	A
WAV	84937	42435	2	0.300	A	A	A	A
WAV	84939	42436	2	0.300	A	A	A	A
WAV	85179	42556	2	0.300	A	A	A	A
WAV	85197	42565	2	0.300	A	A	A	A
WAV	85203	42568	2	0.300	A	A	A	A
WAV	85209	42571	2	0.300	A	A	A	A
WAV	85211	42572	2	0.300	A	A	A	A
WAV	85213	42573	2	0.300	A	A	A	A
WAV	85249	42591	2	0.300	A	A	A	A
WAV	85833	42883	2	0.300	A	A	A	A
WAV	85835	42884	2	0.300	A	A	A	A
WAV	85837	42885	2	0.300	A	A	A	A
WAV	85839	42886	2	0.300	A	A	A	A
WAV	85841	42887	2	0.300	A	A	A	A
WAV	85843	42888	2	0.300	A	A	A	A
WAV	85845	42889	2	0.300	A	A	A	A
WAV	85847	42890	2	0.300	A	A	A	A
WAV	85849	42891	2	0.300	A	A	A	A
WAV	85851	42892	2	0.300	A	A	A	A
WAV	85853	42893	2	0.300	A	A	A	A
WAV	85855	42894	2	0.300	A	A	A	A
WAV	85857	42895	2	0.300	A	A	A	A
WAV	85859	42896	2	0.300	A	A	A	A
WAV	85861	42897	2	0.300	A	A	A	A
WAV	85863	42898	2	0.300	A	A	A	A
WAV	85865	42899	2	0.300	A	A	A	A
WAV	85867	42900	2	0.300	A	A	A	A
WAV	85869	42901	2	0.300	A	A	A	A
WAV	85871	42902	2	0.300	A	A	A	A
WAV	85873	42903	2	0.300	A	A	A	A
WAV	85875	42904	2	0.300	A	A	A	A
WAV	85877	42905	2	0.300	A	A	A	A
WAV	85879	42906	2	0.300	A	A	A	A
WAV	132369	65955	2	0.300	A	A	A	A
WAV	132371	65956	2	0.300	A	A	A	A
WAV	132373	65957	2	0.300	A	A	A	A
WAV	132375	65958	2	0.300	A	A	A	A
WAV	171599	85531	2	0.300	A	A	A	A
WAV	171601	85532	2	0.300	A	A	A	A
WAV	171603	85533	2	0.300	A	A	A	A
WAV	171605	85534	2	0.300	A	A	A	A
WAV	171607	85535	2	0.300	A	A	A	A
WAV	171609	85536	2	0.300	A	A	A	A
WAV	171611	85537	2	0.300	A	A	A	A