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 (
    87742, 87744, 87687, 86878, 86877, 87679, 
    87678, 87627, 87626, 87582, 87560, 
    87481, 87361, 87359, 87055, 87054, 
    87037, 87035, 86884, 86823
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00105

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	174182	86823	1	0.000	A	A	A	A
MP3	174290	86877	1	0.000	A	A	A	A
MP3	174292	86878	1	0.000	A	A	A	A
MP3	174304	86884	1	0.000	A	A	A	A
MP3	174606	87035	1	0.000	A	A	A	A
MP3	174610	87037	1	0.000	A	A	A	A
MP3	174644	87054	1	0.000	A	A	A	A
MP3	174646	87055	1	0.000	A	A	A	A
MP3	175254	87359	1	0.000	A	A	A	A
MP3	175258	87361	1	0.000	A	A	A	A
MP3	175498	87481	1	0.000	A	A	A	A
MP3	175656	87560	1	0.000	A	A	A	A
MP3	175700	87582	1	0.000	A	A	A	A
MP3	175788	87626	1	0.000	A	A	A	A
MP3	175790	87627	1	0.000	A	A	A	A
MP3	175892	87678	1	0.000	A	A	A	A
MP3	175894	87679	1	0.000	A	A	A	A
MP3	175910	87687	1	0.000	A	A	A	A
MP3	176020	87742	1	0.000	A	A	A	A
MP3	176024	87744	1	0.000	A	A	A	A
WAV	174183	86823	2	0.300	A	A	A	A
WAV	174291	86877	2	0.300	A	A	A	A
WAV	174293	86878	2	0.300	A	A	A	A
WAV	174305	86884	2	0.300	A	A	A	A
WAV	174607	87035	2	0.300	A	A	A	A
WAV	174611	87037	2	0.300	A	A	A	A
WAV	174645	87054	2	0.300	A	A	A	A
WAV	174647	87055	2	0.300	A	A	A	A
WAV	175255	87359	2	0.300	A	A	A	A
WAV	175259	87361	2	0.300	A	A	A	A
WAV	175499	87481	2	0.300	A	A	A	A
WAV	175657	87560	2	0.300	A	A	A	A
WAV	175701	87582	2	0.300	A	A	A	A
WAV	175789	87626	2	0.300	A	A	A	A
WAV	175791	87627	2	0.300	A	A	A	A
WAV	175893	87678	2	0.300	A	A	A	A
WAV	175895	87679	2	0.300	A	A	A	A
WAV	175911	87687	2	0.300	A	A	A	A
WAV	176021	87742	2	0.300	A	A	A	A
WAV	176025	87744	2	0.300	A	A	A	A