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 (
    96419, 96418, 96417, 96416, 98738, 96370, 
    96369, 96368, 98502, 98501, 98500, 
    98499, 98739, 98703, 98702, 98701, 
    98655, 98654, 96363, 96356
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00102

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	193248	96356	1	0.000	A	A	A	A
MP3	193262	96363	1	0.000	A	A	A	A
MP3	193272	96368	1	0.000	A	A	A	A
MP3	193274	96369	1	0.000	A	A	A	A
MP3	193276	96370	1	0.000	A	A	A	A
MP3	193368	96416	1	0.000	A	A	A	A
MP3	193370	96417	1	0.000	A	A	A	A
MP3	193372	96418	1	0.000	A	A	A	A
MP3	193374	96419	1	0.000	A	A	A	A
MP3	197534	98499	1	0.000	A	A	A	A
MP3	197536	98500	1	0.000	A	A	A	A
MP3	197538	98501	1	0.000	A	A	A	A
MP3	197540	98502	1	0.000	A	A	A	A
MP3	197844	98654	1	0.000	A	A	A	A
MP3	197846	98655	1	0.000	A	A	A	A
MP3	197938	98701	1	0.000	A	A	A	A
MP3	197940	98702	1	0.000	A	A	A	A
MP3	197942	98703	1	0.000	A	A	A	A
MP3	198012	98738	1	0.000	A	A	A	A
MP3	198014	98739	1	0.000	A	A	A	A
WAV	193249	96356	2	0.300	A	A	A	A
WAV	193263	96363	2	0.300	A	A	A	A
WAV	193273	96368	2	0.300	A	A	A	A
WAV	193275	96369	2	0.300	A	A	A	A
WAV	193277	96370	2	0.300	A	A	A	A
WAV	193369	96416	2	0.300	A	A	A	A
WAV	193371	96417	2	0.300	A	A	A	A
WAV	193373	96418	2	0.300	A	A	A	A
WAV	193375	96419	2	0.300	A	A	A	A
WAV	197535	98499	2	0.300	A	A	A	A
WAV	197537	98500	2	0.300	A	A	A	A
WAV	197539	98501	2	0.300	A	A	A	A
WAV	197541	98502	2	0.300	A	A	A	A
WAV	197845	98654	2	0.300	A	A	A	A
WAV	197847	98655	2	0.300	A	A	A	A
WAV	197939	98701	2	0.300	A	A	A	A
WAV	197941	98702	2	0.300	A	A	A	A
WAV	197943	98703	2	0.300	A	A	A	A
WAV	198013	98738	2	0.300	A	A	A	A
WAV	198015	98739	2	0.300	A	A	A	A