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 (
    17099, 14100, 7906, 8061, 7978, 7970, 
    8043, 7695, 7581, 7434, 7489, 7301, 
    7304, 7034, 6909, 6906, 6863, 6856, 
    6791, 6786
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00098

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	13571	6786	1	0.000	A	A	A	A
MP3	13581	6791	1	0.000	A	A	A	A
MP3	13711	6856	1	0.000	A	A	A	A
MP3	13725	6863	1	0.000	A	A	A	A
MP3	13811	6906	1	0.000	A	A	A	A
MP3	13817	6909	1	0.000	A	A	A	A
MP3	14067	7034	1	0.000	A	A	A	A
MP3	14601	7301	1	0.000	A	A	A	A
MP3	14607	7304	1	0.000	A	A	A	A
MP3	14867	7434	1	0.000	A	A	A	A
MP3	14977	7489	1	0.000	A	A	A	A
MP3	15161	7581	1	0.000	A	A	A	A
MP3	15389	7695	1	0.000	A	A	A	A
MP3	15811	7906	1	0.000	A	A	A	A
MP3	15939	7970	1	0.000	A	A	A	A
MP3	15955	7978	1	0.000	A	A	A	A
MP3	16085	8043	1	0.000	A	A	A	A
MP3	16121	8061	1	0.000	A	A	A	A
MP3	28199	14100	1	0.000	A	A	A	A
MP3	34197	17099	1	0.000	A	A	A	A
WAV	13572	6786	2	0.300	A	A	A	A
WAV	13582	6791	2	0.300	A	A	A	A
WAV	13712	6856	2	0.300	A	A	A	A
WAV	13726	6863	2	0.300	A	A	A	A
WAV	13812	6906	2	0.300	A	A	A	A
WAV	13818	6909	2	0.300	A	A	A	A
WAV	14068	7034	2	0.300	A	A	A	A
WAV	14602	7301	2	0.300	A	A	A	A
WAV	14608	7304	2	0.300	A	A	A	A
WAV	14868	7434	2	0.300	A	A	A	A
WAV	14978	7489	2	0.300	A	A	A	A
WAV	15162	7581	2	0.300	A	A	A	A
WAV	15390	7695	2	0.300	A	A	A	A
WAV	15812	7906	2	0.300	A	A	A	A
WAV	15940	7970	2	0.300	A	A	A	A
WAV	15956	7978	2	0.300	A	A	A	A
WAV	16086	8043	2	0.300	A	A	A	A
WAV	16122	8061	2	0.300	A	A	A	A
WAV	28200	14100	2	0.300	A	A	A	A
WAV	34198	17099	2	0.300	A	A	A	A