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 (
    88840, 88821, 87559, 87049, 89163, 87411, 
    89065, 89043, 88253, 88626, 87437, 
    89008, 89006, 89005, 89004, 89003, 
    89002, 89001, 88996, 88995
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00168

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	174634	87049	1	0.000	A	A	A	A
MP3	175358	87411	1	0.000	A	A	A	A
MP3	175410	87437	1	0.000	A	A	A	A
MP3	175654	87559	1	0.000	A	A	A	A
MP3	177042	88253	1	0.000	A	A	A	A
MP3	177788	88626	1	0.000	A	A	A	A
MP3	178178	88821	1	0.000	A	A	A	A
MP3	178216	88840	1	0.000	A	A	A	A
MP3	178526	88995	1	0.000	A	A	A	A
MP3	178528	88996	1	0.000	A	A	A	A
MP3	178538	89001	1	0.000	A	A	A	A
MP3	178540	89002	1	0.000	A	A	A	A
MP3	178542	89003	1	0.000	A	A	A	A
MP3	178544	89004	1	0.000	A	A	A	A
MP3	178546	89005	1	0.000	A	A	A	A
MP3	178548	89006	1	0.000	A	A	A	A
MP3	178552	89008	1	0.000	A	A	A	A
MP3	178622	89043	1	0.000	A	A	A	A
MP3	178666	89065	1	0.000	A	A	A	A
MP3	178862	89163	1	0.000	A	A	A	A
WAV	174635	87049	2	0.300	A	A	A	A
WAV	175359	87411	2	0.300	A	A	A	A
WAV	175411	87437	2	0.300	A	A	A	A
WAV	175655	87559	2	0.300	A	A	A	A
WAV	177043	88253	2	0.300	A	A	A	A
WAV	177789	88626	2	0.300	A	A	A	A
WAV	178179	88821	2	0.300	A	A	A	A
WAV	178217	88840	2	0.300	A	A	A	A
WAV	178527	88995	2	0.300	A	A	A	A
WAV	178529	88996	2	0.300	A	A	A	A
WAV	178539	89001	2	0.300	A	A	A	A
WAV	178541	89002	2	0.300	A	A	A	A
WAV	178543	89003	2	0.300	A	A	A	A
WAV	178545	89004	2	0.300	A	A	A	A
WAV	178547	89005	2	0.300	A	A	A	A
WAV	178549	89006	2	0.300	A	A	A	A
WAV	178553	89008	2	0.300	A	A	A	A
WAV	178623	89043	2	0.300	A	A	A	A
WAV	178667	89065	2	0.300	A	A	A	A
WAV	178863	89163	2	0.300	A	A	A	A