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 (
    35393, 74833, 35535, 35534, 35385, 35384, 
    35380, 35702, 34965, 34964, 34963, 
    35539, 35538, 35007, 34952, 74847, 
    34945, 34944, 34943, 35298, 35533, 
    35532, 34935, 34934, 34930, 34929, 
    34928, 34849, 34889, 34888, 35615, 
    34884, 34880, 34879, 34877, 34876, 
    34875, 34874, 34866, 34865, 34859, 
    34870, 34787, 34786, 34785, 34777, 
    34774, 34762, 34760, 34758, 34757, 
    34756, 34755, 34754, 34753, 34752, 
    34751, 34750, 34749, 34748, 34747, 
    34743, 34731, 74837, 34883, 34771, 
    34741, 34705, 34699, 34690, 34689, 
    85626, 85625, 85624, 85623, 85622, 
    85621, 85620, 85619, 85618
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00205

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	69444	34689	1	0.000	A	A	A	A
MP3	69446	34690	1	0.000	A	A	A	A
MP3	69464	34699	1	0.000	A	A	A	A
MP3	69476	34705	1	0.000	A	A	A	A
MP3	69528	34731	1	0.000	A	A	A	A
MP3	69548	34741	1	0.000	A	A	A	A
MP3	69552	34743	1	0.000	A	A	A	A
MP3	69560	34747	1	0.000	A	A	A	A
MP3	69562	34748	1	0.000	A	A	A	A
MP3	69564	34749	1	0.000	A	A	A	A
MP3	69566	34750	1	0.000	A	A	A	A
MP3	69568	34751	1	0.000	A	A	A	A
MP3	69570	34752	1	0.000	A	A	A	A
MP3	69572	34753	1	0.000	A	A	A	A
MP3	69574	34754	1	0.000	A	A	A	A
MP3	69576	34755	1	0.000	A	A	A	A
MP3	69578	34756	1	0.000	A	A	A	A
MP3	69580	34757	1	0.000	A	A	A	A
MP3	69582	34758	1	0.000	A	A	A	A
MP3	69586	34760	1	0.000	A	A	A	A
MP3	69590	34762	1	0.000	A	A	A	A
MP3	69608	34771	1	0.000	A	A	A	A
MP3	69614	34774	1	0.000	A	A	A	A
MP3	69620	34777	1	0.000	A	A	A	A
MP3	69636	34785	1	0.000	A	A	A	A
MP3	69638	34786	1	0.000	A	A	A	A
MP3	69640	34787	1	0.000	A	A	A	A
MP3	69764	34849	1	0.000	A	A	A	A
MP3	69784	34859	1	0.000	A	A	A	A
MP3	69796	34865	1	0.000	A	A	A	A
MP3	69798	34866	1	0.000	A	A	A	A
MP3	69806	34870	1	0.000	A	A	A	A
MP3	69814	34874	1	0.000	A	A	A	A
MP3	69816	34875	1	0.000	A	A	A	A
MP3	69818	34876	1	0.000	A	A	A	A
MP3	69820	34877	1	0.000	A	A	A	A
MP3	69824	34879	1	0.000	A	A	A	A
MP3	69826	34880	1	0.000	A	A	A	A
MP3	69832	34883	1	0.000	A	A	A	A
MP3	69834	34884	1	0.000	A	A	A	A
MP3	69842	34888	1	0.000	A	A	A	A
MP3	69844	34889	1	0.000	A	A	A	A
MP3	69922	34928	1	0.000	A	A	A	A
MP3	69924	34929	1	0.000	A	A	A	A
MP3	69926	34930	1	0.000	A	A	A	A
MP3	69934	34934	1	0.000	A	A	A	A
MP3	69936	34935	1	0.000	A	A	A	A
MP3	69952	34943	1	0.000	A	A	A	A
MP3	69954	34944	1	0.000	A	A	A	A
MP3	69956	34945	1	0.000	A	A	A	A
MP3	69970	34952	1	0.000	A	A	A	A
MP3	69992	34963	1	0.000	A	A	A	A
MP3	69994	34964	1	0.000	A	A	A	A
MP3	69996	34965	1	0.000	A	A	A	A
MP3	70080	35007	1	0.000	A	A	A	A
MP3	70662	35298	1	0.000	A	A	A	A
MP3	70826	35380	1	0.000	A	A	A	A
MP3	70834	35384	1	0.000	A	A	A	A
MP3	70836	35385	1	0.000	A	A	A	A
MP3	70852	35393	1	0.000	A	A	A	A
MP3	71130	35532	1	0.000	A	A	A	A
MP3	71132	35533	1	0.000	A	A	A	A
MP3	71134	35534	1	0.000	A	A	A	A
MP3	71136	35535	1	0.000	A	A	A	A
MP3	71142	35538	1	0.000	A	A	A	A
MP3	71144	35539	1	0.000	A	A	A	A
MP3	71296	35615	1	0.000	A	A	A	A
MP3	71470	35702	1	0.000	A	A	A	A
MP3	150202	74833	1	0.000	A	A	A	A
MP3	150210	74837	1	0.000	A	A	A	A
MP3	150230	74847	1	0.000	A	A	A	A
MP3	171772	85618	1	0.000	A	A	A	A
MP3	171774	85619	1	0.000	A	A	A	A
MP3	171776	85620	1	0.000	A	A	A	A
MP3	171778	85621	1	0.000	A	A	A	A
MP3	171780	85622	1	0.000	A	A	A	A
MP3	171782	85623	1	0.000	A	A	A	A
MP3	171784	85624	1	0.000	A	A	A	A
MP3	171786	85625	1	0.000	A	A	A	A
MP3	171788	85626	1	0.000	A	A	A	A
WAV	69445	34689	2	0.300	A	A	A	A
WAV	69447	34690	2	0.300	A	A	A	A
WAV	69465	34699	2	0.300	A	A	A	A
WAV	69477	34705	2	0.300	A	A	A	A
WAV	69529	34731	2	0.300	A	A	A	A
WAV	69549	34741	2	0.300	A	A	A	A
WAV	69553	34743	2	0.300	A	A	A	A
WAV	69561	34747	2	0.300	A	A	A	A
WAV	69563	34748	2	0.300	A	A	A	A
WAV	69565	34749	2	0.300	A	A	A	A
WAV	69567	34750	2	0.300	A	A	A	A
WAV	69569	34751	2	0.300	A	A	A	A
WAV	69571	34752	2	0.300	A	A	A	A
WAV	69573	34753	2	0.300	A	A	A	A
WAV	69575	34754	2	0.300	A	A	A	A
WAV	69577	34755	2	0.300	A	A	A	A
WAV	69579	34756	2	0.300	A	A	A	A
WAV	69581	34757	2	0.300	A	A	A	A
WAV	69583	34758	2	0.300	A	A	A	A
WAV	69587	34760	2	0.300	A	A	A	A
WAV	69591	34762	2	0.300	A	A	A	A
WAV	69609	34771	2	0.300	A	A	A	A
WAV	69615	34774	2	0.300	A	A	A	A
WAV	69621	34777	2	0.300	A	A	A	A
WAV	69637	34785	2	0.300	A	A	A	A
WAV	69639	34786	2	0.300	A	A	A	A
WAV	69641	34787	2	0.300	A	A	A	A
WAV	69765	34849	2	0.300	A	A	A	A
WAV	69785	34859	2	0.300	A	A	A	A
WAV	69797	34865	2	0.300	A	A	A	A
WAV	69799	34866	2	0.300	A	A	A	A
WAV	69807	34870	2	0.300	A	A	A	A
WAV	69815	34874	2	0.300	A	A	A	A
WAV	69817	34875	2	0.300	A	A	A	A
WAV	69819	34876	2	0.300	A	A	A	A
WAV	69821	34877	2	0.300	A	A	A	A
WAV	69825	34879	2	0.300	A	A	A	A
WAV	69827	34880	2	0.300	A	A	A	A
WAV	69833	34883	2	0.300	A	A	A	A
WAV	69835	34884	2	0.300	A	A	A	A
WAV	69843	34888	2	0.300	A	A	A	A
WAV	69845	34889	2	0.300	A	A	A	A
WAV	69923	34928	2	0.300	A	A	A	A
WAV	69925	34929	2	0.300	A	A	A	A
WAV	69927	34930	2	0.300	A	A	A	A
WAV	69935	34934	2	0.300	A	A	A	A
WAV	69937	34935	2	0.300	A	A	A	A
WAV	69953	34943	2	0.300	A	A	A	A
WAV	69955	34944	2	0.300	A	A	A	A
WAV	69957	34945	2	0.300	A	A	A	A
WAV	69971	34952	2	0.300	A	A	A	A
WAV	69993	34963	2	0.300	A	A	A	A
WAV	69995	34964	2	0.300	A	A	A	A
WAV	69997	34965	2	0.300	A	A	A	A
WAV	70081	35007	2	0.300	A	A	A	A
WAV	70663	35298	2	0.300	A	A	A	A
WAV	70827	35380	2	0.300	A	A	A	A
WAV	70835	35384	2	0.300	A	A	A	A
WAV	70837	35385	2	0.300	A	A	A	A
WAV	70853	35393	2	0.300	A	A	A	A
WAV	71131	35532	2	0.300	A	A	A	A
WAV	71133	35533	2	0.300	A	A	A	A
WAV	71135	35534	2	0.300	A	A	A	A
WAV	71137	35535	2	0.300	A	A	A	A
WAV	71143	35538	2	0.300	A	A	A	A
WAV	71145	35539	2	0.300	A	A	A	A
WAV	71297	35615	2	0.300	A	A	A	A
WAV	71471	35702	2	0.300	A	A	A	A
WAV	150203	74833	2	0.300	A	A	A	A
WAV	150211	74837	2	0.300	A	A	A	A
WAV	150231	74847	2	0.300	A	A	A	A
WAV	171773	85618	2	0.300	A	A	A	A
WAV	171775	85619	2	0.300	A	A	A	A
WAV	171777	85620	2	0.300	A	A	A	A
WAV	171779	85621	2	0.300	A	A	A	A
WAV	171781	85622	2	0.300	A	A	A	A
WAV	171783	85623	2	0.300	A	A	A	A
WAV	171785	85624	2	0.300	A	A	A	A
WAV	171787	85625	2	0.300	A	A	A	A
WAV	171789	85626	2	0.300	A	A	A	A