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 (
    43654, 43792, 43629, 43628, 43627, 43626, 
    43624, 43620, 43619, 43598, 43595, 
    43594, 43593, 43592, 43591, 43581, 
    43561, 43560, 43497, 43496, 43495, 
    43494, 43493, 103904, 103903, 103902, 
    103901, 43559, 43558, 43557, 43556, 
    43555, 43554, 43553, 43552, 43551, 
    43550, 43549, 43548, 43477, 43475, 
    43474, 43470, 43469, 43468, 43467, 
    43466, 43465, 43464, 43463, 43462, 
    43461, 43764, 43667, 43498, 43766, 
    43434, 43399, 43398, 43397, 43396, 
    43871, 43870, 43869, 43868, 43867, 
    43866, 43865, 43864, 43863, 43862, 
    43861, 43860, 43859, 43858, 43857, 
    43856, 43855, 43854, 43853
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00296

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	86858	43396	1	0.000	A	A	A	A
MP3	86860	43397	1	0.000	A	A	A	A
MP3	86862	43398	1	0.000	A	A	A	A
MP3	86864	43399	1	0.000	A	A	A	A
MP3	86934	43434	1	0.000	A	A	A	A
MP3	86988	43461	1	0.000	A	A	A	A
MP3	86990	43462	1	0.000	A	A	A	A
MP3	86992	43463	1	0.000	A	A	A	A
MP3	86994	43464	1	0.000	A	A	A	A
MP3	86996	43465	1	0.000	A	A	A	A
MP3	86998	43466	1	0.000	A	A	A	A
MP3	87000	43467	1	0.000	A	A	A	A
MP3	87002	43468	1	0.000	A	A	A	A
MP3	87004	43469	1	0.000	A	A	A	A
MP3	87006	43470	1	0.000	A	A	A	A
MP3	87014	43474	1	0.000	A	A	A	A
MP3	87016	43475	1	0.000	A	A	A	A
MP3	87020	43477	1	0.000	A	A	A	A
MP3	87052	43493	1	0.000	A	A	A	A
MP3	87054	43494	1	0.000	A	A	A	A
MP3	87056	43495	1	0.000	A	A	A	A
MP3	87058	43496	1	0.000	A	A	A	A
MP3	87060	43497	1	0.000	A	A	A	A
MP3	87062	43498	1	0.000	A	A	A	A
MP3	87162	43548	1	0.000	A	A	A	A
MP3	87164	43549	1	0.000	A	A	A	A
MP3	87166	43550	1	0.000	A	A	A	A
MP3	87168	43551	1	0.000	A	A	A	A
MP3	87170	43552	1	0.000	A	A	A	A
MP3	87172	43553	1	0.000	A	A	A	A
MP3	87174	43554	1	0.000	A	A	A	A
MP3	87176	43555	1	0.000	A	A	A	A
MP3	87178	43556	1	0.000	A	A	A	A
MP3	87180	43557	1	0.000	A	A	A	A
MP3	87182	43558	1	0.000	A	A	A	A
MP3	87184	43559	1	0.000	A	A	A	A
MP3	87186	43560	1	0.000	A	A	A	A
MP3	87188	43561	1	0.000	A	A	A	A
MP3	87228	43581	1	0.000	A	A	A	A
MP3	87248	43591	1	0.000	A	A	A	A
MP3	87250	43592	1	0.000	A	A	A	A
MP3	87252	43593	1	0.000	A	A	A	A
MP3	87254	43594	1	0.000	A	A	A	A
MP3	87256	43595	1	0.000	A	A	A	A
MP3	87262	43598	1	0.000	A	A	A	A
MP3	87304	43619	1	0.000	A	A	A	A
MP3	87306	43620	1	0.000	A	A	A	A
MP3	87314	43624	1	0.000	A	A	A	A
MP3	87318	43626	1	0.000	A	A	A	A
MP3	87320	43627	1	0.000	A	A	A	A
MP3	87322	43628	1	0.000	A	A	A	A
MP3	87324	43629	1	0.000	A	A	A	A
MP3	87374	43654	1	0.000	A	A	A	A
MP3	87400	43667	1	0.000	A	A	A	A
MP3	87594	43764	1	0.000	A	A	A	A
MP3	87598	43766	1	0.000	A	A	A	A
MP3	87650	43792	1	0.000	A	A	A	A
MP3	87772	43853	1	0.000	A	A	A	A
MP3	87774	43854	1	0.000	A	A	A	A
MP3	87776	43855	1	0.000	A	A	A	A
MP3	87778	43856	1	0.000	A	A	A	A
MP3	87780	43857	1	0.000	A	A	A	A
MP3	87782	43858	1	0.000	A	A	A	A
MP3	87784	43859	1	0.000	A	A	A	A
MP3	87786	43860	1	0.000	A	A	A	A
MP3	87788	43861	1	0.000	A	A	A	A
MP3	87790	43862	1	0.000	A	A	A	A
MP3	87792	43863	1	0.000	A	A	A	A
MP3	87794	43864	1	0.000	A	A	A	A
MP3	87796	43865	1	0.000	A	A	A	A
MP3	87798	43866	1	0.000	A	A	A	A
MP3	87800	43867	1	0.000	A	A	A	A
MP3	87802	43868	1	0.000	A	A	A	A
MP3	87804	43869	1	0.000	A	A	A	A
MP3	87806	43870	1	0.000	A	A	A	A
MP3	87808	43871	1	0.000	A	A	A	A
MP3	208338	103901	1	0.000	A	A	A	A
MP3	208340	103902	1	0.000	A	A	A	A
MP3	208342	103903	1	0.000	A	A	A	A
MP3	208344	103904	1	0.000	A	A	A	A
WAV	86859	43396	2	0.300	A	A	A	A
WAV	86861	43397	2	0.300	A	A	A	A
WAV	86863	43398	2	0.300	A	A	A	A
WAV	86865	43399	2	0.300	A	A	A	A
WAV	86935	43434	2	0.300	A	A	A	A
WAV	86989	43461	2	0.300	A	A	A	A
WAV	86991	43462	2	0.300	A	A	A	A
WAV	86993	43463	2	0.300	A	A	A	A
WAV	86995	43464	2	0.300	A	A	A	A
WAV	86997	43465	2	0.300	A	A	A	A
WAV	86999	43466	2	0.300	A	A	A	A
WAV	87001	43467	2	0.300	A	A	A	A
WAV	87003	43468	2	0.300	A	A	A	A
WAV	87005	43469	2	0.300	A	A	A	A
WAV	87007	43470	2	0.300	A	A	A	A
WAV	87015	43474	2	0.300	A	A	A	A
WAV	87017	43475	2	0.300	A	A	A	A
WAV	87021	43477	2	0.300	A	A	A	A
WAV	87053	43493	2	0.300	A	A	A	A
WAV	87055	43494	2	0.300	A	A	A	A
WAV	87057	43495	2	0.300	A	A	A	A
WAV	87059	43496	2	0.300	A	A	A	A
WAV	87061	43497	2	0.300	A	A	A	A
WAV	87063	43498	2	0.300	A	A	A	A
WAV	87163	43548	2	0.300	A	A	A	A
WAV	87165	43549	2	0.300	A	A	A	A
WAV	87167	43550	2	0.300	A	A	A	A
WAV	87169	43551	2	0.300	A	A	A	A
WAV	87171	43552	2	0.300	A	A	A	A
WAV	87173	43553	2	0.300	A	A	A	A
WAV	87175	43554	2	0.300	A	A	A	A
WAV	87177	43555	2	0.300	A	A	A	A
WAV	87179	43556	2	0.300	A	A	A	A
WAV	87181	43557	2	0.300	A	A	A	A
WAV	87183	43558	2	0.300	A	A	A	A
WAV	87185	43559	2	0.300	A	A	A	A
WAV	87187	43560	2	0.300	A	A	A	A
WAV	87189	43561	2	0.300	A	A	A	A
WAV	87229	43581	2	0.300	A	A	A	A
WAV	87249	43591	2	0.300	A	A	A	A
WAV	87251	43592	2	0.300	A	A	A	A
WAV	87253	43593	2	0.300	A	A	A	A
WAV	87255	43594	2	0.300	A	A	A	A
WAV	87257	43595	2	0.300	A	A	A	A
WAV	87263	43598	2	0.300	A	A	A	A
WAV	87305	43619	2	0.300	A	A	A	A
WAV	87307	43620	2	0.300	A	A	A	A
WAV	87315	43624	2	0.300	A	A	A	A
WAV	87319	43626	2	0.300	A	A	A	A
WAV	87321	43627	2	0.300	A	A	A	A
WAV	87323	43628	2	0.300	A	A	A	A
WAV	87325	43629	2	0.300	A	A	A	A
WAV	87375	43654	2	0.300	A	A	A	A
WAV	87401	43667	2	0.300	A	A	A	A
WAV	87595	43764	2	0.300	A	A	A	A
WAV	87599	43766	2	0.300	A	A	A	A
WAV	87651	43792	2	0.300	A	A	A	A
WAV	87773	43853	2	0.300	A	A	A	A
WAV	87775	43854	2	0.300	A	A	A	A
WAV	87777	43855	2	0.300	A	A	A	A
WAV	87779	43856	2	0.300	A	A	A	A
WAV	87781	43857	2	0.300	A	A	A	A
WAV	87783	43858	2	0.300	A	A	A	A
WAV	87785	43859	2	0.300	A	A	A	A
WAV	87787	43860	2	0.300	A	A	A	A
WAV	87789	43861	2	0.300	A	A	A	A
WAV	87791	43862	2	0.300	A	A	A	A
WAV	87793	43863	2	0.300	A	A	A	A
WAV	87795	43864	2	0.300	A	A	A	A
WAV	87797	43865	2	0.300	A	A	A	A
WAV	87799	43866	2	0.300	A	A	A	A
WAV	87801	43867	2	0.300	A	A	A	A
WAV	87803	43868	2	0.300	A	A	A	A
WAV	87805	43869	2	0.300	A	A	A	A
WAV	87807	43870	2	0.300	A	A	A	A
WAV	87809	43871	2	0.300	A	A	A	A
WAV	208339	103901	2	0.300	A	A	A	A
WAV	208341	103902	2	0.300	A	A	A	A
WAV	208343	103903	2	0.300	A	A	A	A
WAV	208345	103904	2	0.300	A	A	A	A