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 (
    42056, 41810, 41808, 45705, 42021, 41824, 
    41640, 45703, 41633, 41629, 41628, 
    41627, 41626, 41625, 41624, 41623, 
    41622, 41621, 41620, 41619, 41618, 
    41617, 41811, 41696, 41694, 41693, 
    41692, 41691, 41690, 41689, 41688, 
    41687, 41686, 41685, 41684, 41683, 
    41682, 41517, 41532, 41531, 41530, 
    41791, 41790, 41789, 41788, 41787, 
    41786, 41785, 41784, 41783, 41782, 
    41781, 41780, 41779, 41778, 41777, 
    41776, 41773, 41522, 41463, 41462, 
    41504, 41376, 41339, 41529, 41528, 
    41527, 41526, 41496, 41297, 41594, 
    41593, 41592, 41521, 41515, 41514, 
    41513, 41512, 41282, 41511
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00169

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	82630	41282	1	0.000	A	A	A	A
MP3	82660	41297	1	0.000	A	A	A	A
MP3	82744	41339	1	0.000	A	A	A	A
MP3	82818	41376	1	0.000	A	A	A	A
MP3	82990	41462	1	0.000	A	A	A	A
MP3	82992	41463	1	0.000	A	A	A	A
MP3	83058	41496	1	0.000	A	A	A	A
MP3	83074	41504	1	0.000	A	A	A	A
MP3	83088	41511	1	0.000	A	A	A	A
MP3	83090	41512	1	0.000	A	A	A	A
MP3	83092	41513	1	0.000	A	A	A	A
MP3	83094	41514	1	0.000	A	A	A	A
MP3	83096	41515	1	0.000	A	A	A	A
MP3	83100	41517	1	0.000	A	A	A	A
MP3	83108	41521	1	0.000	A	A	A	A
MP3	83110	41522	1	0.000	A	A	A	A
MP3	83118	41526	1	0.000	A	A	A	A
MP3	83120	41527	1	0.000	A	A	A	A
MP3	83122	41528	1	0.000	A	A	A	A
MP3	83124	41529	1	0.000	A	A	A	A
MP3	83126	41530	1	0.000	A	A	A	A
MP3	83128	41531	1	0.000	A	A	A	A
MP3	83130	41532	1	0.000	A	A	A	A
MP3	83250	41592	1	0.000	A	A	A	A
MP3	83252	41593	1	0.000	A	A	A	A
MP3	83254	41594	1	0.000	A	A	A	A
MP3	83300	41617	1	0.000	A	A	A	A
MP3	83302	41618	1	0.000	A	A	A	A
MP3	83304	41619	1	0.000	A	A	A	A
MP3	83306	41620	1	0.000	A	A	A	A
MP3	83308	41621	1	0.000	A	A	A	A
MP3	83310	41622	1	0.000	A	A	A	A
MP3	83312	41623	1	0.000	A	A	A	A
MP3	83314	41624	1	0.000	A	A	A	A
MP3	83316	41625	1	0.000	A	A	A	A
MP3	83318	41626	1	0.000	A	A	A	A
MP3	83320	41627	1	0.000	A	A	A	A
MP3	83322	41628	1	0.000	A	A	A	A
MP3	83324	41629	1	0.000	A	A	A	A
MP3	83332	41633	1	0.000	A	A	A	A
MP3	83346	41640	1	0.000	A	A	A	A
MP3	83430	41682	1	0.000	A	A	A	A
MP3	83432	41683	1	0.000	A	A	A	A
MP3	83434	41684	1	0.000	A	A	A	A
MP3	83436	41685	1	0.000	A	A	A	A
MP3	83438	41686	1	0.000	A	A	A	A
MP3	83440	41687	1	0.000	A	A	A	A
MP3	83442	41688	1	0.000	A	A	A	A
MP3	83444	41689	1	0.000	A	A	A	A
MP3	83446	41690	1	0.000	A	A	A	A
MP3	83448	41691	1	0.000	A	A	A	A
MP3	83450	41692	1	0.000	A	A	A	A
MP3	83452	41693	1	0.000	A	A	A	A
MP3	83454	41694	1	0.000	A	A	A	A
MP3	83458	41696	1	0.000	A	A	A	A
MP3	83612	41773	1	0.000	A	A	A	A
MP3	83618	41776	1	0.000	A	A	A	A
MP3	83620	41777	1	0.000	A	A	A	A
MP3	83622	41778	1	0.000	A	A	A	A
MP3	83624	41779	1	0.000	A	A	A	A
MP3	83626	41780	1	0.000	A	A	A	A
MP3	83628	41781	1	0.000	A	A	A	A
MP3	83630	41782	1	0.000	A	A	A	A
MP3	83632	41783	1	0.000	A	A	A	A
MP3	83634	41784	1	0.000	A	A	A	A
MP3	83636	41785	1	0.000	A	A	A	A
MP3	83638	41786	1	0.000	A	A	A	A
MP3	83640	41787	1	0.000	A	A	A	A
MP3	83642	41788	1	0.000	A	A	A	A
MP3	83644	41789	1	0.000	A	A	A	A
MP3	83646	41790	1	0.000	A	A	A	A
MP3	83648	41791	1	0.000	A	A	A	A
MP3	83682	41808	1	0.000	A	A	A	A
MP3	83686	41810	1	0.000	A	A	A	A
MP3	83688	41811	1	0.000	A	A	A	A
MP3	83714	41824	1	0.000	A	A	A	A
MP3	84108	42021	1	0.000	A	A	A	A
MP3	84178	42056	1	0.000	A	A	A	A
MP3	91472	45703	1	0.000	A	A	A	A
MP3	91476	45705	1	0.000	A	A	A	A
WAV	82631	41282	2	0.300	A	A	A	A
WAV	82661	41297	2	0.300	A	A	A	A
WAV	82745	41339	2	0.300	A	A	A	A
WAV	82819	41376	2	0.300	A	A	A	A
WAV	82991	41462	2	0.300	A	A	A	A
WAV	82993	41463	2	0.300	A	A	A	A
WAV	83059	41496	2	0.300	A	A	A	A
WAV	83075	41504	2	0.300	A	A	A	A
WAV	83089	41511	2	0.300	A	A	A	A
WAV	83091	41512	2	0.300	A	A	A	A
WAV	83093	41513	2	0.300	A	A	A	A
WAV	83095	41514	2	0.300	A	A	A	A
WAV	83097	41515	2	0.300	A	A	A	A
WAV	83101	41517	2	0.300	A	A	A	A
WAV	83109	41521	2	0.300	A	A	A	A
WAV	83111	41522	2	0.300	A	A	A	A
WAV	83119	41526	2	0.300	A	A	A	A
WAV	83121	41527	2	0.300	A	A	A	A
WAV	83123	41528	2	0.300	A	A	A	A
WAV	83125	41529	2	0.300	A	A	A	A
WAV	83127	41530	2	0.300	A	A	A	A
WAV	83129	41531	2	0.300	A	A	A	A
WAV	83131	41532	2	0.300	A	A	A	A
WAV	83251	41592	2	0.300	A	A	A	A
WAV	83253	41593	2	0.300	A	A	A	A
WAV	83255	41594	2	0.300	A	A	A	A
WAV	83301	41617	2	0.300	A	A	A	A
WAV	83303	41618	2	0.300	A	A	A	A
WAV	83305	41619	2	0.300	A	A	A	A
WAV	83307	41620	2	0.300	A	A	A	A
WAV	83309	41621	2	0.300	A	A	A	A
WAV	83311	41622	2	0.300	A	A	A	A
WAV	83313	41623	2	0.300	A	A	A	A
WAV	83315	41624	2	0.300	A	A	A	A
WAV	83317	41625	2	0.300	A	A	A	A
WAV	83319	41626	2	0.300	A	A	A	A
WAV	83321	41627	2	0.300	A	A	A	A
WAV	83323	41628	2	0.300	A	A	A	A
WAV	83325	41629	2	0.300	A	A	A	A
WAV	83333	41633	2	0.300	A	A	A	A
WAV	83347	41640	2	0.300	A	A	A	A
WAV	83431	41682	2	0.300	A	A	A	A
WAV	83433	41683	2	0.300	A	A	A	A
WAV	83435	41684	2	0.300	A	A	A	A
WAV	83437	41685	2	0.300	A	A	A	A
WAV	83439	41686	2	0.300	A	A	A	A
WAV	83441	41687	2	0.300	A	A	A	A
WAV	83443	41688	2	0.300	A	A	A	A
WAV	83445	41689	2	0.300	A	A	A	A
WAV	83447	41690	2	0.300	A	A	A	A
WAV	83449	41691	2	0.300	A	A	A	A
WAV	83451	41692	2	0.300	A	A	A	A
WAV	83453	41693	2	0.300	A	A	A	A
WAV	83455	41694	2	0.300	A	A	A	A
WAV	83459	41696	2	0.300	A	A	A	A
WAV	83613	41773	2	0.300	A	A	A	A
WAV	83619	41776	2	0.300	A	A	A	A
WAV	83621	41777	2	0.300	A	A	A	A
WAV	83623	41778	2	0.300	A	A	A	A
WAV	83625	41779	2	0.300	A	A	A	A
WAV	83627	41780	2	0.300	A	A	A	A
WAV	83629	41781	2	0.300	A	A	A	A
WAV	83631	41782	2	0.300	A	A	A	A
WAV	83633	41783	2	0.300	A	A	A	A
WAV	83635	41784	2	0.300	A	A	A	A
WAV	83637	41785	2	0.300	A	A	A	A
WAV	83639	41786	2	0.300	A	A	A	A
WAV	83641	41787	2	0.300	A	A	A	A
WAV	83643	41788	2	0.300	A	A	A	A
WAV	83645	41789	2	0.300	A	A	A	A
WAV	83647	41790	2	0.300	A	A	A	A
WAV	83649	41791	2	0.300	A	A	A	A
WAV	83683	41808	2	0.300	A	A	A	A
WAV	83687	41810	2	0.300	A	A	A	A
WAV	83689	41811	2	0.300	A	A	A	A
WAV	83715	41824	2	0.300	A	A	A	A
WAV	84109	42021	2	0.300	A	A	A	A
WAV	84179	42056	2	0.300	A	A	A	A
WAV	91473	45703	2	0.300	A	A	A	A
WAV	91477	45705	2	0.300	A	A	A	A