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 (
    99511, 99510, 99509, 99508, 99507, 99506, 
    99505, 99504, 98498, 98458, 98449, 
    98448, 98447, 98446, 98445, 98444, 
    98443, 98425, 98424, 98423
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00073

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	197382	98423	1	0.000	A	A	A	A
MP3	197384	98424	1	0.000	A	A	A	A
MP3	197386	98425	1	0.000	A	A	A	A
MP3	197422	98443	1	0.000	A	A	A	A
MP3	197424	98444	1	0.000	A	A	A	A
MP3	197426	98445	1	0.000	A	A	A	A
MP3	197428	98446	1	0.000	A	A	A	A
MP3	197430	98447	1	0.000	A	A	A	A
MP3	197432	98448	1	0.000	A	A	A	A
MP3	197434	98449	1	0.000	A	A	A	A
MP3	197452	98458	1	0.000	A	A	A	A
MP3	197532	98498	1	0.000	A	A	A	A
MP3	199544	99504	1	0.000	A	A	A	A
MP3	199546	99505	1	0.000	A	A	A	A
MP3	199548	99506	1	0.000	A	A	A	A
MP3	199550	99507	1	0.000	A	A	A	A
MP3	199552	99508	1	0.000	A	A	A	A
MP3	199554	99509	1	0.000	A	A	A	A
MP3	199556	99510	1	0.000	A	A	A	A
MP3	199558	99511	1	0.000	A	A	A	A
WAV	197383	98423	2	0.300	A	A	A	A
WAV	197385	98424	2	0.300	A	A	A	A
WAV	197387	98425	2	0.300	A	A	A	A
WAV	197423	98443	2	0.300	A	A	A	A
WAV	197425	98444	2	0.300	A	A	A	A
WAV	197427	98445	2	0.300	A	A	A	A
WAV	197429	98446	2	0.300	A	A	A	A
WAV	197431	98447	2	0.300	A	A	A	A
WAV	197433	98448	2	0.300	A	A	A	A
WAV	197435	98449	2	0.300	A	A	A	A
WAV	197453	98458	2	0.300	A	A	A	A
WAV	197533	98498	2	0.300	A	A	A	A
WAV	199545	99504	2	0.300	A	A	A	A
WAV	199547	99505	2	0.300	A	A	A	A
WAV	199549	99506	2	0.300	A	A	A	A
WAV	199551	99507	2	0.300	A	A	A	A
WAV	199553	99508	2	0.300	A	A	A	A
WAV	199555	99509	2	0.300	A	A	A	A
WAV	199557	99510	2	0.300	A	A	A	A
WAV	199559	99511	2	0.300	A	A	A	A