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 (
    8700, 9317, 9315, 9314, 9012, 8540, 8539, 
    8680, 8473, 8414, 8207, 8103, 8151, 
    8140, 8150, 8145, 8099, 14085, 14084, 
    7931
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00084

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	15861	7931	1	0.000	A	A	A	A
MP3	16197	8099	1	0.000	A	A	A	A
MP3	16205	8103	1	0.000	A	A	A	A
MP3	16279	8140	1	0.000	A	A	A	A
MP3	16289	8145	1	0.000	A	A	A	A
MP3	16299	8150	1	0.000	A	A	A	A
MP3	16301	8151	1	0.000	A	A	A	A
MP3	16413	8207	1	0.000	A	A	A	A
MP3	16827	8414	1	0.000	A	A	A	A
MP3	16945	8473	1	0.000	A	A	A	A
MP3	17077	8539	1	0.000	A	A	A	A
MP3	17079	8540	1	0.000	A	A	A	A
MP3	17359	8680	1	0.000	A	A	A	A
MP3	17399	8700	1	0.000	A	A	A	A
MP3	18023	9012	1	0.000	A	A	A	A
MP3	18627	9314	1	0.000	A	A	A	A
MP3	18629	9315	1	0.000	A	A	A	A
MP3	18633	9317	1	0.000	A	A	A	A
MP3	28167	14084	1	0.000	A	A	A	A
MP3	28169	14085	1	0.000	A	A	A	A
WAV	15862	7931	2	0.300	A	A	A	A
WAV	16198	8099	2	0.300	A	A	A	A
WAV	16206	8103	2	0.300	A	A	A	A
WAV	16280	8140	2	0.300	A	A	A	A
WAV	16290	8145	2	0.300	A	A	A	A
WAV	16300	8150	2	0.300	A	A	A	A
WAV	16302	8151	2	0.300	A	A	A	A
WAV	16414	8207	2	0.300	A	A	A	A
WAV	16828	8414	2	0.300	A	A	A	A
WAV	16946	8473	2	0.300	A	A	A	A
WAV	17078	8539	2	0.300	A	A	A	A
WAV	17080	8540	2	0.300	A	A	A	A
WAV	17360	8680	2	0.300	A	A	A	A
WAV	17400	8700	2	0.300	A	A	A	A
WAV	18024	9012	2	0.300	A	A	A	A
WAV	18628	9314	2	0.300	A	A	A	A
WAV	18630	9315	2	0.300	A	A	A	A
WAV	18634	9317	2	0.300	A	A	A	A
WAV	28168	14084	2	0.300	A	A	A	A
WAV	28170	14085	2	0.300	A	A	A	A