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 (
    87516, 87515, 87514, 87513, 87512, 87511, 
    87510, 87509, 87508, 87507, 87506, 
    87505, 87504, 87503, 87502, 87501, 
    87500, 87499, 87498, 87497
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00137

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	175530	87497	1	0.000	A	A	A	A
MP3	175532	87498	1	0.000	A	A	A	A
MP3	175534	87499	1	0.000	A	A	A	A
MP3	175536	87500	1	0.000	A	A	A	A
MP3	175538	87501	1	0.000	A	A	A	A
MP3	175540	87502	1	0.000	A	A	A	A
MP3	175542	87503	1	0.000	A	A	A	A
MP3	175544	87504	1	0.000	A	A	A	A
MP3	175546	87505	1	0.000	A	A	A	A
MP3	175548	87506	1	0.000	A	A	A	A
MP3	175550	87507	1	0.000	A	A	A	A
MP3	175552	87508	1	0.000	A	A	A	A
MP3	175554	87509	1	0.000	A	A	A	A
MP3	175556	87510	1	0.000	A	A	A	A
MP3	175558	87511	1	0.000	A	A	A	A
MP3	175560	87512	1	0.000	A	A	A	A
MP3	175562	87513	1	0.000	A	A	A	A
MP3	175564	87514	1	0.000	A	A	A	A
MP3	175566	87515	1	0.000	A	A	A	A
MP3	175568	87516	1	0.000	A	A	A	A
WAV	175531	87497	2	0.300	A	A	A	A
WAV	175533	87498	2	0.300	A	A	A	A
WAV	175535	87499	2	0.300	A	A	A	A
WAV	175537	87500	2	0.300	A	A	A	A
WAV	175539	87501	2	0.300	A	A	A	A
WAV	175541	87502	2	0.300	A	A	A	A
WAV	175543	87503	2	0.300	A	A	A	A
WAV	175545	87504	2	0.300	A	A	A	A
WAV	175547	87505	2	0.300	A	A	A	A
WAV	175549	87506	2	0.300	A	A	A	A
WAV	175551	87507	2	0.300	A	A	A	A
WAV	175553	87508	2	0.300	A	A	A	A
WAV	175555	87509	2	0.300	A	A	A	A
WAV	175557	87510	2	0.300	A	A	A	A
WAV	175559	87511	2	0.300	A	A	A	A
WAV	175561	87512	2	0.300	A	A	A	A
WAV	175563	87513	2	0.300	A	A	A	A
WAV	175565	87514	2	0.300	A	A	A	A
WAV	175567	87515	2	0.300	A	A	A	A
WAV	175569	87516	2	0.300	A	A	A	A