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 (
    10309, 10308, 10305, 10304, 10303, 10302, 
    10301, 10300, 10299, 10298, 10297, 
    10296, 10295, 10133, 10132, 10134, 
    19885, 19884, 10068, 10066, 10070, 
    10069, 10044, 11800, 10042, 10043, 
    9765, 9801, 9800, 26544, 26543, 26542, 
    26541, 26540, 26539, 26538, 26537, 
    26536, 26535, 26534
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00152

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	19529	9765	1	0.000	A	A	A	A
MP3	19599	9800	1	0.000	A	A	A	A
MP3	19601	9801	1	0.000	A	A	A	A
MP3	20083	10042	1	0.000	A	A	A	A
MP3	20085	10043	1	0.000	A	A	A	A
MP3	20087	10044	1	0.000	A	A	A	A
MP3	20131	10066	1	0.000	A	A	A	A
MP3	20135	10068	1	0.000	A	A	A	A
MP3	20137	10069	1	0.000	A	A	A	A
MP3	20139	10070	1	0.000	A	A	A	A
MP3	20263	10132	1	0.000	A	A	A	A
MP3	20265	10133	1	0.000	A	A	A	A
MP3	20267	10134	1	0.000	A	A	A	A
MP3	20589	10295	1	0.000	A	A	A	A
MP3	20591	10296	1	0.000	A	A	A	A
MP3	20593	10297	1	0.000	A	A	A	A
MP3	20595	10298	1	0.000	A	A	A	A
MP3	20597	10299	1	0.000	A	A	A	A
MP3	20599	10300	1	0.000	A	A	A	A
MP3	20601	10301	1	0.000	A	A	A	A
MP3	20603	10302	1	0.000	A	A	A	A
MP3	20605	10303	1	0.000	A	A	A	A
MP3	20607	10304	1	0.000	A	A	A	A
MP3	20609	10305	1	0.000	A	A	A	A
MP3	20615	10308	1	0.000	A	A	A	A
MP3	20617	10309	1	0.000	A	A	A	A
MP3	23599	11800	1	0.000	A	A	A	A
MP3	39767	19884	1	0.000	A	A	A	A
MP3	39769	19885	1	0.000	A	A	A	A
MP3	53067	26534	1	0.000	A	A	A	A
MP3	53069	26535	1	0.000	A	A	A	A
MP3	53071	26536	1	0.000	A	A	A	A
MP3	53073	26537	1	0.000	A	A	A	A
MP3	53075	26538	1	0.000	A	A	A	A
MP3	53077	26539	1	0.000	A	A	A	A
MP3	53079	26540	1	0.000	A	A	A	A
MP3	53081	26541	1	0.000	A	A	A	A
MP3	53083	26542	1	0.000	A	A	A	A
MP3	53085	26543	1	0.000	A	A	A	A
MP3	53087	26544	1	0.000	A	A	A	A
WAV	19530	9765	2	0.300	A	A	A	A
WAV	19600	9800	2	0.300	A	A	A	A
WAV	19602	9801	2	0.300	A	A	A	A
WAV	20084	10042	2	0.300	A	A	A	A
WAV	20086	10043	2	0.300	A	A	A	A
WAV	20088	10044	2	0.300	A	A	A	A
WAV	20132	10066	2	0.300	A	A	A	A
WAV	20136	10068	2	0.300	A	A	A	A
WAV	20138	10069	2	0.300	A	A	A	A
WAV	20140	10070	2	0.300	A	A	A	A
WAV	20264	10132	2	0.300	A	A	A	A
WAV	20266	10133	2	0.300	A	A	A	A
WAV	20268	10134	2	0.300	A	A	A	A
WAV	20590	10295	2	0.300	A	A	A	A
WAV	20592	10296	2	0.300	A	A	A	A
WAV	20594	10297	2	0.300	A	A	A	A
WAV	20596	10298	2	0.300	A	A	A	A
WAV	20598	10299	2	0.300	A	A	A	A
WAV	20600	10300	2	0.300	A	A	A	A
WAV	20602	10301	2	0.300	A	A	A	A
WAV	20604	10302	2	0.300	A	A	A	A
WAV	20606	10303	2	0.300	A	A	A	A
WAV	20608	10304	2	0.300	A	A	A	A
WAV	20610	10305	2	0.300	A	A	A	A
WAV	20616	10308	2	0.300	A	A	A	A
WAV	20618	10309	2	0.300	A	A	A	A
WAV	23600	11800	2	0.300	A	A	A	A
WAV	39768	19884	2	0.300	A	A	A	A
WAV	39770	19885	2	0.300	A	A	A	A
WAV	53068	26534	2	0.300	A	A	A	A
WAV	53070	26535	2	0.300	A	A	A	A
WAV	53072	26536	2	0.300	A	A	A	A
WAV	53074	26537	2	0.300	A	A	A	A
WAV	53076	26538	2	0.300	A	A	A	A
WAV	53078	26539	2	0.300	A	A	A	A
WAV	53080	26540	2	0.300	A	A	A	A
WAV	53082	26541	2	0.300	A	A	A	A
WAV	53084	26542	2	0.300	A	A	A	A
WAV	53086	26543	2	0.300	A	A	A	A
WAV	53088	26544	2	0.300	A	A	A	A