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 (
    52516, 52515, 52514, 52418, 52396, 52395, 
    52388, 52387, 52386, 52385, 52384, 
    52383, 52382, 52381, 52380, 52379, 
    52378, 52377, 52376, 52375, 52374, 
    52805, 52336, 52331, 52328, 52311, 
    52570, 52569, 52568, 52567, 52566, 
    52565, 52564, 52563, 52562, 52561, 
    52560, 52310, 52301, 52300, 52299, 
    52298, 52297, 52296, 52680, 52521, 
    52274, 52252, 52251, 52250, 52237, 
    52236, 52235, 52234, 52256, 52255, 
    52192, 52191, 52190, 52189, 52188, 
    52187, 52182, 52181, 52180, 52179, 
    52532, 52531, 52530, 52281, 52178, 
    52170, 52168, 52156, 52176, 52116, 
    81482, 81481, 81480, 81479
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00208

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	104298	52116	1	0.000	A	A	A	A
MP3	104378	52156	1	0.000	A	A	A	A
MP3	104402	52168	1	0.000	A	A	A	A
MP3	104406	52170	1	0.000	A	A	A	A
MP3	104418	52176	1	0.000	A	A	A	A
MP3	104422	52178	1	0.000	A	A	A	A
MP3	104424	52179	1	0.000	A	A	A	A
MP3	104426	52180	1	0.000	A	A	A	A
MP3	104428	52181	1	0.000	A	A	A	A
MP3	104430	52182	1	0.000	A	A	A	A
MP3	104440	52187	1	0.000	A	A	A	A
MP3	104442	52188	1	0.000	A	A	A	A
MP3	104444	52189	1	0.000	A	A	A	A
MP3	104446	52190	1	0.000	A	A	A	A
MP3	104448	52191	1	0.000	A	A	A	A
MP3	104450	52192	1	0.000	A	A	A	A
MP3	104534	52234	1	0.000	A	A	A	A
MP3	104536	52235	1	0.000	A	A	A	A
MP3	104538	52236	1	0.000	A	A	A	A
MP3	104540	52237	1	0.000	A	A	A	A
MP3	104566	52250	1	0.000	A	A	A	A
MP3	104568	52251	1	0.000	A	A	A	A
MP3	104570	52252	1	0.000	A	A	A	A
MP3	104576	52255	1	0.000	A	A	A	A
MP3	104578	52256	1	0.000	A	A	A	A
MP3	104614	52274	1	0.000	A	A	A	A
MP3	104628	52281	1	0.000	A	A	A	A
MP3	104658	52296	1	0.000	A	A	A	A
MP3	104660	52297	1	0.000	A	A	A	A
MP3	104662	52298	1	0.000	A	A	A	A
MP3	104664	52299	1	0.000	A	A	A	A
MP3	104666	52300	1	0.000	A	A	A	A
MP3	104668	52301	1	0.000	A	A	A	A
MP3	104686	52310	1	0.000	A	A	A	A
MP3	104688	52311	1	0.000	A	A	A	A
MP3	104722	52328	1	0.000	A	A	A	A
MP3	104728	52331	1	0.000	A	A	A	A
MP3	104738	52336	1	0.000	A	A	A	A
MP3	104814	52374	1	0.000	A	A	A	A
MP3	104816	52375	1	0.000	A	A	A	A
MP3	104818	52376	1	0.000	A	A	A	A
MP3	104820	52377	1	0.000	A	A	A	A
MP3	104822	52378	1	0.000	A	A	A	A
MP3	104824	52379	1	0.000	A	A	A	A
MP3	104826	52380	1	0.000	A	A	A	A
MP3	104828	52381	1	0.000	A	A	A	A
MP3	104830	52382	1	0.000	A	A	A	A
MP3	104832	52383	1	0.000	A	A	A	A
MP3	104834	52384	1	0.000	A	A	A	A
MP3	104836	52385	1	0.000	A	A	A	A
MP3	104838	52386	1	0.000	A	A	A	A
MP3	104840	52387	1	0.000	A	A	A	A
MP3	104842	52388	1	0.000	A	A	A	A
MP3	104856	52395	1	0.000	A	A	A	A
MP3	104858	52396	1	0.000	A	A	A	A
MP3	104902	52418	1	0.000	A	A	A	A
MP3	105094	52514	1	0.000	A	A	A	A
MP3	105096	52515	1	0.000	A	A	A	A
MP3	105098	52516	1	0.000	A	A	A	A
MP3	105108	52521	1	0.000	A	A	A	A
MP3	105126	52530	1	0.000	A	A	A	A
MP3	105128	52531	1	0.000	A	A	A	A
MP3	105130	52532	1	0.000	A	A	A	A
MP3	105186	52560	1	0.000	A	A	A	A
MP3	105188	52561	1	0.000	A	A	A	A
MP3	105190	52562	1	0.000	A	A	A	A
MP3	105192	52563	1	0.000	A	A	A	A
MP3	105194	52564	1	0.000	A	A	A	A
MP3	105196	52565	1	0.000	A	A	A	A
MP3	105198	52566	1	0.000	A	A	A	A
MP3	105200	52567	1	0.000	A	A	A	A
MP3	105202	52568	1	0.000	A	A	A	A
MP3	105204	52569	1	0.000	A	A	A	A
MP3	105206	52570	1	0.000	A	A	A	A
MP3	105426	52680	1	0.000	A	A	A	A
MP3	105676	52805	1	0.000	A	A	A	A
MP3	163494	81479	1	0.000	A	A	A	A
MP3	163496	81480	1	0.000	A	A	A	A
MP3	163498	81481	1	0.000	A	A	A	A
MP3	163500	81482	1	0.000	A	A	A	A
WAV	104299	52116	2	0.300	A	A	A	A
WAV	104379	52156	2	0.300	A	A	A	A
WAV	104403	52168	2	0.300	A	A	A	A
WAV	104407	52170	2	0.300	A	A	A	A
WAV	104419	52176	2	0.300	A	A	A	A
WAV	104423	52178	2	0.300	A	A	A	A
WAV	104425	52179	2	0.300	A	A	A	A
WAV	104427	52180	2	0.300	A	A	A	A
WAV	104429	52181	2	0.300	A	A	A	A
WAV	104431	52182	2	0.300	A	A	A	A
WAV	104441	52187	2	0.300	A	A	A	A
WAV	104443	52188	2	0.300	A	A	A	A
WAV	104445	52189	2	0.300	A	A	A	A
WAV	104447	52190	2	0.300	A	A	A	A
WAV	104449	52191	2	0.300	A	A	A	A
WAV	104451	52192	2	0.300	A	A	A	A
WAV	104535	52234	2	0.300	A	A	A	A
WAV	104537	52235	2	0.300	A	A	A	A
WAV	104539	52236	2	0.300	A	A	A	A
WAV	104541	52237	2	0.300	A	A	A	A
WAV	104567	52250	2	0.300	A	A	A	A
WAV	104569	52251	2	0.300	A	A	A	A
WAV	104571	52252	2	0.300	A	A	A	A
WAV	104577	52255	2	0.300	A	A	A	A
WAV	104579	52256	2	0.300	A	A	A	A
WAV	104615	52274	2	0.300	A	A	A	A
WAV	104629	52281	2	0.300	A	A	A	A
WAV	104659	52296	2	0.300	A	A	A	A
WAV	104661	52297	2	0.300	A	A	A	A
WAV	104663	52298	2	0.300	A	A	A	A
WAV	104665	52299	2	0.300	A	A	A	A
WAV	104667	52300	2	0.300	A	A	A	A
WAV	104669	52301	2	0.300	A	A	A	A
WAV	104687	52310	2	0.300	A	A	A	A
WAV	104689	52311	2	0.300	A	A	A	A
WAV	104723	52328	2	0.300	A	A	A	A
WAV	104729	52331	2	0.300	A	A	A	A
WAV	104739	52336	2	0.300	A	A	A	A
WAV	104815	52374	2	0.300	A	A	A	A
WAV	104817	52375	2	0.300	A	A	A	A
WAV	104819	52376	2	0.300	A	A	A	A
WAV	104821	52377	2	0.300	A	A	A	A
WAV	104823	52378	2	0.300	A	A	A	A
WAV	104825	52379	2	0.300	A	A	A	A
WAV	104827	52380	2	0.300	A	A	A	A
WAV	104829	52381	2	0.300	A	A	A	A
WAV	104831	52382	2	0.300	A	A	A	A
WAV	104833	52383	2	0.300	A	A	A	A
WAV	104835	52384	2	0.300	A	A	A	A
WAV	104837	52385	2	0.300	A	A	A	A
WAV	104839	52386	2	0.300	A	A	A	A
WAV	104841	52387	2	0.300	A	A	A	A
WAV	104843	52388	2	0.300	A	A	A	A
WAV	104857	52395	2	0.300	A	A	A	A
WAV	104859	52396	2	0.300	A	A	A	A
WAV	104903	52418	2	0.300	A	A	A	A
WAV	105095	52514	2	0.300	A	A	A	A
WAV	105097	52515	2	0.300	A	A	A	A
WAV	105099	52516	2	0.300	A	A	A	A
WAV	105109	52521	2	0.300	A	A	A	A
WAV	105127	52530	2	0.300	A	A	A	A
WAV	105129	52531	2	0.300	A	A	A	A
WAV	105131	52532	2	0.300	A	A	A	A
WAV	105187	52560	2	0.300	A	A	A	A
WAV	105189	52561	2	0.300	A	A	A	A
WAV	105191	52562	2	0.300	A	A	A	A
WAV	105193	52563	2	0.300	A	A	A	A
WAV	105195	52564	2	0.300	A	A	A	A
WAV	105197	52565	2	0.300	A	A	A	A
WAV	105199	52566	2	0.300	A	A	A	A
WAV	105201	52567	2	0.300	A	A	A	A
WAV	105203	52568	2	0.300	A	A	A	A
WAV	105205	52569	2	0.300	A	A	A	A
WAV	105207	52570	2	0.300	A	A	A	A
WAV	105427	52680	2	0.300	A	A	A	A
WAV	105677	52805	2	0.300	A	A	A	A
WAV	163495	81479	2	0.300	A	A	A	A
WAV	163497	81480	2	0.300	A	A	A	A
WAV	163499	81481	2	0.300	A	A	A	A
WAV	163501	81482	2	0.300	A	A	A	A