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 (
    96649, 96648, 96647, 96646, 96645, 96644, 
    96643, 96642, 96641, 96640, 96639, 
    96620, 96618, 96617, 96616, 96615, 
    96614, 96613, 96612, 96611
  ) 
  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	193758	96611	1	0.000	A	A	A	A
MP3	193760	96612	1	0.000	A	A	A	A
MP3	193762	96613	1	0.000	A	A	A	A
MP3	193764	96614	1	0.000	A	A	A	A
MP3	193766	96615	1	0.000	A	A	A	A
MP3	193768	96616	1	0.000	A	A	A	A
MP3	193770	96617	1	0.000	A	A	A	A
MP3	193772	96618	1	0.000	A	A	A	A
MP3	193776	96620	1	0.000	A	A	A	A
MP3	193814	96639	1	0.000	A	A	A	A
MP3	193816	96640	1	0.000	A	A	A	A
MP3	193818	96641	1	0.000	A	A	A	A
MP3	193820	96642	1	0.000	A	A	A	A
MP3	193822	96643	1	0.000	A	A	A	A
MP3	193824	96644	1	0.000	A	A	A	A
MP3	193826	96645	1	0.000	A	A	A	A
MP3	193828	96646	1	0.000	A	A	A	A
MP3	193830	96647	1	0.000	A	A	A	A
MP3	193832	96648	1	0.000	A	A	A	A
MP3	193834	96649	1	0.000	A	A	A	A
WAV	193759	96611	2	0.300	A	A	A	A
WAV	193761	96612	2	0.300	A	A	A	A
WAV	193763	96613	2	0.300	A	A	A	A
WAV	193765	96614	2	0.300	A	A	A	A
WAV	193767	96615	2	0.300	A	A	A	A
WAV	193769	96616	2	0.300	A	A	A	A
WAV	193771	96617	2	0.300	A	A	A	A
WAV	193773	96618	2	0.300	A	A	A	A
WAV	193777	96620	2	0.300	A	A	A	A
WAV	193815	96639	2	0.300	A	A	A	A
WAV	193817	96640	2	0.300	A	A	A	A
WAV	193819	96641	2	0.300	A	A	A	A
WAV	193821	96642	2	0.300	A	A	A	A
WAV	193823	96643	2	0.300	A	A	A	A
WAV	193825	96644	2	0.300	A	A	A	A
WAV	193827	96645	2	0.300	A	A	A	A
WAV	193829	96646	2	0.300	A	A	A	A
WAV	193831	96647	2	0.300	A	A	A	A
WAV	193833	96648	2	0.300	A	A	A	A
WAV	193835	96649	2	0.300	A	A	A	A