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 (
    95659, 95996, 96268, 96267, 96228, 96206, 
    96130, 96129, 96128, 96127, 96126, 
    96125, 96124, 96123, 96122, 96121, 
    96120, 96119, 96118, 96117
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00112

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	191854	95659	1	0.000	A	A	A	A
MP3	192528	95996	1	0.000	A	A	A	A
MP3	192770	96117	1	0.000	A	A	A	A
MP3	192772	96118	1	0.000	A	A	A	A
MP3	192774	96119	1	0.000	A	A	A	A
MP3	192776	96120	1	0.000	A	A	A	A
MP3	192778	96121	1	0.000	A	A	A	A
MP3	192780	96122	1	0.000	A	A	A	A
MP3	192782	96123	1	0.000	A	A	A	A
MP3	192784	96124	1	0.000	A	A	A	A
MP3	192786	96125	1	0.000	A	A	A	A
MP3	192788	96126	1	0.000	A	A	A	A
MP3	192790	96127	1	0.000	A	A	A	A
MP3	192792	96128	1	0.000	A	A	A	A
MP3	192794	96129	1	0.000	A	A	A	A
MP3	192796	96130	1	0.000	A	A	A	A
MP3	192948	96206	1	0.000	A	A	A	A
MP3	192992	96228	1	0.000	A	A	A	A
MP3	193070	96267	1	0.000	A	A	A	A
MP3	193072	96268	1	0.000	A	A	A	A
WAV	191855	95659	2	0.300	A	A	A	A
WAV	192529	95996	2	0.300	A	A	A	A
WAV	192771	96117	2	0.300	A	A	A	A
WAV	192773	96118	2	0.300	A	A	A	A
WAV	192775	96119	2	0.300	A	A	A	A
WAV	192777	96120	2	0.300	A	A	A	A
WAV	192779	96121	2	0.300	A	A	A	A
WAV	192781	96122	2	0.300	A	A	A	A
WAV	192783	96123	2	0.300	A	A	A	A
WAV	192785	96124	2	0.300	A	A	A	A
WAV	192787	96125	2	0.300	A	A	A	A
WAV	192789	96126	2	0.300	A	A	A	A
WAV	192791	96127	2	0.300	A	A	A	A
WAV	192793	96128	2	0.300	A	A	A	A
WAV	192795	96129	2	0.300	A	A	A	A
WAV	192797	96130	2	0.300	A	A	A	A
WAV	192949	96206	2	0.300	A	A	A	A
WAV	192993	96228	2	0.300	A	A	A	A
WAV	193071	96267	2	0.300	A	A	A	A
WAV	193073	96268	2	0.300	A	A	A	A