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 (
    10476, 10475, 33875, 10465, 10464, 18478, 
    18471, 10452, 10379, 10372, 10389, 
    10387, 10385, 10382, 10381, 10445, 
    10360, 71393, 71392, 10344, 10331, 
    10330, 10329, 10328, 10327, 10326, 
    10325, 10324, 10322, 10321, 10320, 
    10319, 10317, 10316, 10315, 10314, 
    10313, 10312, 10311, 10310
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00163

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		80	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	20619	10310	1	0.000	A	A	A	A
MP3	20621	10311	1	0.000	A	A	A	A
MP3	20623	10312	1	0.000	A	A	A	A
MP3	20625	10313	1	0.000	A	A	A	A
MP3	20627	10314	1	0.000	A	A	A	A
MP3	20629	10315	1	0.000	A	A	A	A
MP3	20631	10316	1	0.000	A	A	A	A
MP3	20633	10317	1	0.000	A	A	A	A
MP3	20637	10319	1	0.000	A	A	A	A
MP3	20639	10320	1	0.000	A	A	A	A
MP3	20641	10321	1	0.000	A	A	A	A
MP3	20643	10322	1	0.000	A	A	A	A
MP3	20647	10324	1	0.000	A	A	A	A
MP3	20649	10325	1	0.000	A	A	A	A
MP3	20651	10326	1	0.000	A	A	A	A
MP3	20653	10327	1	0.000	A	A	A	A
MP3	20655	10328	1	0.000	A	A	A	A
MP3	20657	10329	1	0.000	A	A	A	A
MP3	20659	10330	1	0.000	A	A	A	A
MP3	20661	10331	1	0.000	A	A	A	A
MP3	20687	10344	1	0.000	A	A	A	A
MP3	20719	10360	1	0.000	A	A	A	A
MP3	20743	10372	1	0.000	A	A	A	A
MP3	20757	10379	1	0.000	A	A	A	A
MP3	20761	10381	1	0.000	A	A	A	A
MP3	20763	10382	1	0.000	A	A	A	A
MP3	20769	10385	1	0.000	A	A	A	A
MP3	20773	10387	1	0.000	A	A	A	A
MP3	20777	10389	1	0.000	A	A	A	A
MP3	20889	10445	1	0.000	A	A	A	A
MP3	20903	10452	1	0.000	A	A	A	A
MP3	20927	10464	1	0.000	A	A	A	A
MP3	20929	10465	1	0.000	A	A	A	A
MP3	20949	10475	1	0.000	A	A	A	A
MP3	20951	10476	1	0.000	A	A	A	A
MP3	36941	18471	1	0.000	A	A	A	A
MP3	36955	18478	1	0.000	A	A	A	A
MP3	67816	33875	1	0.000	A	A	A	A
MP3	143298	71392	1	0.000	A	A	A	A
MP3	143300	71393	1	0.000	A	A	A	A
WAV	20620	10310	2	0.300	A	A	A	A
WAV	20622	10311	2	0.300	A	A	A	A
WAV	20624	10312	2	0.300	A	A	A	A
WAV	20626	10313	2	0.300	A	A	A	A
WAV	20628	10314	2	0.300	A	A	A	A
WAV	20630	10315	2	0.300	A	A	A	A
WAV	20632	10316	2	0.300	A	A	A	A
WAV	20634	10317	2	0.300	A	A	A	A
WAV	20638	10319	2	0.300	A	A	A	A
WAV	20640	10320	2	0.300	A	A	A	A
WAV	20642	10321	2	0.300	A	A	A	A
WAV	20644	10322	2	0.300	A	A	A	A
WAV	20648	10324	2	0.300	A	A	A	A
WAV	20650	10325	2	0.300	A	A	A	A
WAV	20652	10326	2	0.300	A	A	A	A
WAV	20654	10327	2	0.300	A	A	A	A
WAV	20656	10328	2	0.300	A	A	A	A
WAV	20658	10329	2	0.300	A	A	A	A
WAV	20660	10330	2	0.300	A	A	A	A
WAV	20662	10331	2	0.300	A	A	A	A
WAV	20688	10344	2	0.300	A	A	A	A
WAV	20720	10360	2	0.300	A	A	A	A
WAV	20744	10372	2	0.300	A	A	A	A
WAV	20758	10379	2	0.300	A	A	A	A
WAV	20762	10381	2	0.300	A	A	A	A
WAV	20764	10382	2	0.300	A	A	A	A
WAV	20770	10385	2	0.300	A	A	A	A
WAV	20774	10387	2	0.300	A	A	A	A
WAV	20778	10389	2	0.300	A	A	A	A
WAV	20890	10445	2	0.300	A	A	A	A
WAV	20904	10452	2	0.300	A	A	A	A
WAV	20928	10464	2	0.300	A	A	A	A
WAV	20930	10465	2	0.300	A	A	A	A
WAV	20950	10475	2	0.300	A	A	A	A
WAV	20952	10476	2	0.300	A	A	A	A
WAV	36942	18471	2	0.300	A	A	A	A
WAV	36956	18478	2	0.300	A	A	A	A
WAV	67817	33875	2	0.300	A	A	A	A
WAV	143299	71392	2	0.300	A	A	A	A
WAV	143301	71393	2	0.300	A	A	A	A