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 (
    81799, 81798, 81797, 81796, 81795, 81794, 
    81793, 81792, 81791, 81790, 81789, 
    81788, 81787, 81786, 81785, 81784, 
    81783, 81782, 81781, 81780
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00070

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	164096	81780	1	0.000	A	A	A	A
MP3	164098	81781	1	0.000	A	A	A	A
MP3	164100	81782	1	0.000	A	A	A	A
MP3	164102	81783	1	0.000	A	A	A	A
MP3	164104	81784	1	0.000	A	A	A	A
MP3	164106	81785	1	0.000	A	A	A	A
MP3	164108	81786	1	0.000	A	A	A	A
MP3	164110	81787	1	0.000	A	A	A	A
MP3	164112	81788	1	0.000	A	A	A	A
MP3	164114	81789	1	0.000	A	A	A	A
MP3	164116	81790	1	0.000	A	A	A	A
MP3	164118	81791	1	0.000	A	A	A	A
MP3	164120	81792	1	0.000	A	A	A	A
MP3	164122	81793	1	0.000	A	A	A	A
MP3	164124	81794	1	0.000	A	A	A	A
MP3	164126	81795	1	0.000	A	A	A	A
MP3	164128	81796	1	0.000	A	A	A	A
MP3	164130	81797	1	0.000	A	A	A	A
MP3	164132	81798	1	0.000	A	A	A	A
MP3	164134	81799	1	0.000	A	A	A	A
WAV	164097	81780	2	0.300	A	A	A	A
WAV	164099	81781	2	0.300	A	A	A	A
WAV	164101	81782	2	0.300	A	A	A	A
WAV	164103	81783	2	0.300	A	A	A	A
WAV	164105	81784	2	0.300	A	A	A	A
WAV	164107	81785	2	0.300	A	A	A	A
WAV	164109	81786	2	0.300	A	A	A	A
WAV	164111	81787	2	0.300	A	A	A	A
WAV	164113	81788	2	0.300	A	A	A	A
WAV	164115	81789	2	0.300	A	A	A	A
WAV	164117	81790	2	0.300	A	A	A	A
WAV	164119	81791	2	0.300	A	A	A	A
WAV	164121	81792	2	0.300	A	A	A	A
WAV	164123	81793	2	0.300	A	A	A	A
WAV	164125	81794	2	0.300	A	A	A	A
WAV	164127	81795	2	0.300	A	A	A	A
WAV	164129	81796	2	0.300	A	A	A	A
WAV	164131	81797	2	0.300	A	A	A	A
WAV	164133	81798	2	0.300	A	A	A	A
WAV	164135	81799	2	0.300	A	A	A	A