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 (
    42162, 42161, 42160, 42159, 42158, 42157, 
    42156, 42155, 42154, 42253, 42365, 
    42364, 42363, 42362, 42361, 42360, 
    42359, 42358, 42357, 42356, 42355, 
    42354, 42353, 42352, 42351, 42350, 
    42349, 42348, 42347, 42346, 42345, 
    42344, 42343, 42342, 42341, 42129, 
    42128, 42270, 42269, 42268, 42267, 
    42266, 42265, 42264, 42263, 45704, 
    42254, 42251, 42249, 42248, 42247, 
    42246, 42210, 42115, 42306, 42208, 
    42057, 42053, 42052, 42204, 42046, 
    42200, 42041, 42205, 42020, 41953, 
    41930, 42191, 42180, 42179, 42178, 
    42177, 42176, 42175, 42174, 42173, 
    42172, 42171, 42055, 42045
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00203

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	83926	41930	1	0.000	A	A	A	A
MP3	83972	41953	1	0.000	A	A	A	A
MP3	84106	42020	1	0.000	A	A	A	A
MP3	84148	42041	1	0.000	A	A	A	A
MP3	84156	42045	1	0.000	A	A	A	A
MP3	84158	42046	1	0.000	A	A	A	A
MP3	84170	42052	1	0.000	A	A	A	A
MP3	84172	42053	1	0.000	A	A	A	A
MP3	84176	42055	1	0.000	A	A	A	A
MP3	84180	42057	1	0.000	A	A	A	A
MP3	84296	42115	1	0.000	A	A	A	A
MP3	84322	42128	1	0.000	A	A	A	A
MP3	84324	42129	1	0.000	A	A	A	A
MP3	84374	42154	1	0.000	A	A	A	A
MP3	84376	42155	1	0.000	A	A	A	A
MP3	84378	42156	1	0.000	A	A	A	A
MP3	84380	42157	1	0.000	A	A	A	A
MP3	84382	42158	1	0.000	A	A	A	A
MP3	84384	42159	1	0.000	A	A	A	A
MP3	84386	42160	1	0.000	A	A	A	A
MP3	84388	42161	1	0.000	A	A	A	A
MP3	84390	42162	1	0.000	A	A	A	A
MP3	84408	42171	1	0.000	A	A	A	A
MP3	84410	42172	1	0.000	A	A	A	A
MP3	84412	42173	1	0.000	A	A	A	A
MP3	84414	42174	1	0.000	A	A	A	A
MP3	84416	42175	1	0.000	A	A	A	A
MP3	84418	42176	1	0.000	A	A	A	A
MP3	84420	42177	1	0.000	A	A	A	A
MP3	84422	42178	1	0.000	A	A	A	A
MP3	84424	42179	1	0.000	A	A	A	A
MP3	84426	42180	1	0.000	A	A	A	A
MP3	84448	42191	1	0.000	A	A	A	A
MP3	84466	42200	1	0.000	A	A	A	A
MP3	84474	42204	1	0.000	A	A	A	A
MP3	84476	42205	1	0.000	A	A	A	A
MP3	84482	42208	1	0.000	A	A	A	A
MP3	84486	42210	1	0.000	A	A	A	A
MP3	84558	42246	1	0.000	A	A	A	A
MP3	84560	42247	1	0.000	A	A	A	A
MP3	84562	42248	1	0.000	A	A	A	A
MP3	84564	42249	1	0.000	A	A	A	A
MP3	84568	42251	1	0.000	A	A	A	A
MP3	84572	42253	1	0.000	A	A	A	A
MP3	84574	42254	1	0.000	A	A	A	A
MP3	84592	42263	1	0.000	A	A	A	A
MP3	84594	42264	1	0.000	A	A	A	A
MP3	84596	42265	1	0.000	A	A	A	A
MP3	84598	42266	1	0.000	A	A	A	A
MP3	84600	42267	1	0.000	A	A	A	A
MP3	84602	42268	1	0.000	A	A	A	A
MP3	84604	42269	1	0.000	A	A	A	A
MP3	84606	42270	1	0.000	A	A	A	A
MP3	84678	42306	1	0.000	A	A	A	A
MP3	84748	42341	1	0.000	A	A	A	A
MP3	84750	42342	1	0.000	A	A	A	A
MP3	84752	42343	1	0.000	A	A	A	A
MP3	84754	42344	1	0.000	A	A	A	A
MP3	84756	42345	1	0.000	A	A	A	A
MP3	84758	42346	1	0.000	A	A	A	A
MP3	84760	42347	1	0.000	A	A	A	A
MP3	84762	42348	1	0.000	A	A	A	A
MP3	84764	42349	1	0.000	A	A	A	A
MP3	84766	42350	1	0.000	A	A	A	A
MP3	84768	42351	1	0.000	A	A	A	A
MP3	84770	42352	1	0.000	A	A	A	A
MP3	84772	42353	1	0.000	A	A	A	A
MP3	84774	42354	1	0.000	A	A	A	A
MP3	84776	42355	1	0.000	A	A	A	A
MP3	84778	42356	1	0.000	A	A	A	A
MP3	84780	42357	1	0.000	A	A	A	A
MP3	84782	42358	1	0.000	A	A	A	A
MP3	84784	42359	1	0.000	A	A	A	A
MP3	84786	42360	1	0.000	A	A	A	A
MP3	84788	42361	1	0.000	A	A	A	A
MP3	84790	42362	1	0.000	A	A	A	A
MP3	84792	42363	1	0.000	A	A	A	A
MP3	84794	42364	1	0.000	A	A	A	A
MP3	84796	42365	1	0.000	A	A	A	A
MP3	91474	45704	1	0.000	A	A	A	A
WAV	83927	41930	2	0.300	A	A	A	A
WAV	83973	41953	2	0.300	A	A	A	A
WAV	84107	42020	2	0.300	A	A	A	A
WAV	84149	42041	2	0.300	A	A	A	A
WAV	84157	42045	2	0.300	A	A	A	A
WAV	84159	42046	2	0.300	A	A	A	A
WAV	84171	42052	2	0.300	A	A	A	A
WAV	84173	42053	2	0.300	A	A	A	A
WAV	84177	42055	2	0.300	A	A	A	A
WAV	84181	42057	2	0.300	A	A	A	A
WAV	84297	42115	2	0.300	A	A	A	A
WAV	84323	42128	2	0.300	A	A	A	A
WAV	84325	42129	2	0.300	A	A	A	A
WAV	84375	42154	2	0.300	A	A	A	A
WAV	84377	42155	2	0.300	A	A	A	A
WAV	84379	42156	2	0.300	A	A	A	A
WAV	84381	42157	2	0.300	A	A	A	A
WAV	84383	42158	2	0.300	A	A	A	A
WAV	84385	42159	2	0.300	A	A	A	A
WAV	84387	42160	2	0.300	A	A	A	A
WAV	84389	42161	2	0.300	A	A	A	A
WAV	84391	42162	2	0.300	A	A	A	A
WAV	84409	42171	2	0.300	A	A	A	A
WAV	84411	42172	2	0.300	A	A	A	A
WAV	84413	42173	2	0.300	A	A	A	A
WAV	84415	42174	2	0.300	A	A	A	A
WAV	84417	42175	2	0.300	A	A	A	A
WAV	84419	42176	2	0.300	A	A	A	A
WAV	84421	42177	2	0.300	A	A	A	A
WAV	84423	42178	2	0.300	A	A	A	A
WAV	84425	42179	2	0.300	A	A	A	A
WAV	84427	42180	2	0.300	A	A	A	A
WAV	84449	42191	2	0.300	A	A	A	A
WAV	84467	42200	2	0.300	A	A	A	A
WAV	84475	42204	2	0.300	A	A	A	A
WAV	84477	42205	2	0.300	A	A	A	A
WAV	84483	42208	2	0.300	A	A	A	A
WAV	84487	42210	2	0.300	A	A	A	A
WAV	84559	42246	2	0.300	A	A	A	A
WAV	84561	42247	2	0.300	A	A	A	A
WAV	84563	42248	2	0.300	A	A	A	A
WAV	84565	42249	2	0.300	A	A	A	A
WAV	84569	42251	2	0.300	A	A	A	A
WAV	84573	42253	2	0.300	A	A	A	A
WAV	84575	42254	2	0.300	A	A	A	A
WAV	84593	42263	2	0.300	A	A	A	A
WAV	84595	42264	2	0.300	A	A	A	A
WAV	84597	42265	2	0.300	A	A	A	A
WAV	84599	42266	2	0.300	A	A	A	A
WAV	84601	42267	2	0.300	A	A	A	A
WAV	84603	42268	2	0.300	A	A	A	A
WAV	84605	42269	2	0.300	A	A	A	A
WAV	84607	42270	2	0.300	A	A	A	A
WAV	84679	42306	2	0.300	A	A	A	A
WAV	84749	42341	2	0.300	A	A	A	A
WAV	84751	42342	2	0.300	A	A	A	A
WAV	84753	42343	2	0.300	A	A	A	A
WAV	84755	42344	2	0.300	A	A	A	A
WAV	84757	42345	2	0.300	A	A	A	A
WAV	84759	42346	2	0.300	A	A	A	A
WAV	84761	42347	2	0.300	A	A	A	A
WAV	84763	42348	2	0.300	A	A	A	A
WAV	84765	42349	2	0.300	A	A	A	A
WAV	84767	42350	2	0.300	A	A	A	A
WAV	84769	42351	2	0.300	A	A	A	A
WAV	84771	42352	2	0.300	A	A	A	A
WAV	84773	42353	2	0.300	A	A	A	A
WAV	84775	42354	2	0.300	A	A	A	A
WAV	84777	42355	2	0.300	A	A	A	A
WAV	84779	42356	2	0.300	A	A	A	A
WAV	84781	42357	2	0.300	A	A	A	A
WAV	84783	42358	2	0.300	A	A	A	A
WAV	84785	42359	2	0.300	A	A	A	A
WAV	84787	42360	2	0.300	A	A	A	A
WAV	84789	42361	2	0.300	A	A	A	A
WAV	84791	42362	2	0.300	A	A	A	A
WAV	84793	42363	2	0.300	A	A	A	A
WAV	84795	42364	2	0.300	A	A	A	A
WAV	84797	42365	2	0.300	A	A	A	A
WAV	91475	45704	2	0.300	A	A	A	A