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 (
    95966, 95844, 95483, 95835, 95825, 95824, 
    95818, 95663, 95060, 95059, 87475, 
    95734, 95692, 95691, 95690, 95680, 
    95657, 95348, 95159, 95158
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00095

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	175486	87475	1	0.000	A	A	A	A
MP3	190654	95059	1	0.000	A	A	A	A
MP3	190656	95060	1	0.000	A	A	A	A
MP3	190852	95158	1	0.000	A	A	A	A
MP3	190854	95159	1	0.000	A	A	A	A
MP3	191232	95348	1	0.000	A	A	A	A
MP3	191502	95483	1	0.000	A	A	A	A
MP3	191850	95657	1	0.000	A	A	A	A
MP3	191862	95663	1	0.000	A	A	A	A
MP3	191896	95680	1	0.000	A	A	A	A
MP3	191916	95690	1	0.000	A	A	A	A
MP3	191918	95691	1	0.000	A	A	A	A
MP3	191920	95692	1	0.000	A	A	A	A
MP3	192004	95734	1	0.000	A	A	A	A
MP3	192172	95818	1	0.000	A	A	A	A
MP3	192184	95824	1	0.000	A	A	A	A
MP3	192186	95825	1	0.000	A	A	A	A
MP3	192206	95835	1	0.000	A	A	A	A
MP3	192224	95844	1	0.000	A	A	A	A
MP3	192468	95966	1	0.000	A	A	A	A
WAV	175487	87475	2	0.300	A	A	A	A
WAV	190655	95059	2	0.300	A	A	A	A
WAV	190657	95060	2	0.300	A	A	A	A
WAV	190853	95158	2	0.300	A	A	A	A
WAV	190855	95159	2	0.300	A	A	A	A
WAV	191233	95348	2	0.300	A	A	A	A
WAV	191503	95483	2	0.300	A	A	A	A
WAV	191851	95657	2	0.300	A	A	A	A
WAV	191863	95663	2	0.300	A	A	A	A
WAV	191897	95680	2	0.300	A	A	A	A
WAV	191917	95690	2	0.300	A	A	A	A
WAV	191919	95691	2	0.300	A	A	A	A
WAV	191921	95692	2	0.300	A	A	A	A
WAV	192005	95734	2	0.300	A	A	A	A
WAV	192173	95818	2	0.300	A	A	A	A
WAV	192185	95824	2	0.300	A	A	A	A
WAV	192187	95825	2	0.300	A	A	A	A
WAV	192207	95835	2	0.300	A	A	A	A
WAV	192225	95844	2	0.300	A	A	A	A
WAV	192469	95966	2	0.300	A	A	A	A