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 (
    75485, 75466, 74933, 74928, 74927, 74922, 
    75375, 97241, 75352, 74919, 75181, 
    74997, 74996, 74995, 74994, 74993, 
    74992, 74991, 74990, 74989
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00096

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		40	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	150374	74919	1	0.000	A	A	A	A
MP3	150380	74922	1	0.000	A	A	A	A
MP3	150390	74927	1	0.000	A	A	A	A
MP3	150392	74928	1	0.000	A	A	A	A
MP3	150402	74933	1	0.000	A	A	A	A
MP3	150514	74989	1	0.000	A	A	A	A
MP3	150516	74990	1	0.000	A	A	A	A
MP3	150518	74991	1	0.000	A	A	A	A
MP3	150520	74992	1	0.000	A	A	A	A
MP3	150522	74993	1	0.000	A	A	A	A
MP3	150524	74994	1	0.000	A	A	A	A
MP3	150526	74995	1	0.000	A	A	A	A
MP3	150528	74996	1	0.000	A	A	A	A
MP3	150530	74997	1	0.000	A	A	A	A
MP3	150898	75181	1	0.000	A	A	A	A
MP3	151240	75352	1	0.000	A	A	A	A
MP3	151286	75375	1	0.000	A	A	A	A
MP3	151468	75466	1	0.000	A	A	A	A
MP3	151506	75485	1	0.000	A	A	A	A
MP3	195018	97241	1	0.000	A	A	A	A
WAV	150375	74919	2	0.300	A	A	A	A
WAV	150381	74922	2	0.300	A	A	A	A
WAV	150391	74927	2	0.300	A	A	A	A
WAV	150393	74928	2	0.300	A	A	A	A
WAV	150403	74933	2	0.300	A	A	A	A
WAV	150515	74989	2	0.300	A	A	A	A
WAV	150517	74990	2	0.300	A	A	A	A
WAV	150519	74991	2	0.300	A	A	A	A
WAV	150521	74992	2	0.300	A	A	A	A
WAV	150523	74993	2	0.300	A	A	A	A
WAV	150525	74994	2	0.300	A	A	A	A
WAV	150527	74995	2	0.300	A	A	A	A
WAV	150529	74996	2	0.300	A	A	A	A
WAV	150531	74997	2	0.300	A	A	A	A
WAV	150899	75181	2	0.300	A	A	A	A
WAV	151241	75352	2	0.300	A	A	A	A
WAV	151287	75375	2	0.300	A	A	A	A
WAV	151469	75466	2	0.300	A	A	A	A
WAV	151507	75485	2	0.300	A	A	A	A
WAV	195019	97241	2	0.300	A	A	A	A