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 (
    81917, 81916, 81915, 81914, 81913, 81912, 
    81911, 81910, 81909, 81908, 81907, 
    81906, 81696, 81695, 81694, 81693, 
    81692, 81691, 81690, 81689, 81219, 
    81161, 81160, 81159, 81158, 81099, 
    81098, 81097, 81096, 81095, 81094, 
    81093, 81092, 81091, 81090, 81089, 
    81809, 81184, 81183, 81182
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00110

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		80	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	162714	81089	1	0.000	A	A	A	A
MP3	162716	81090	1	0.000	A	A	A	A
MP3	162718	81091	1	0.000	A	A	A	A
MP3	162720	81092	1	0.000	A	A	A	A
MP3	162722	81093	1	0.000	A	A	A	A
MP3	162724	81094	1	0.000	A	A	A	A
MP3	162726	81095	1	0.000	A	A	A	A
MP3	162728	81096	1	0.000	A	A	A	A
MP3	162730	81097	1	0.000	A	A	A	A
MP3	162732	81098	1	0.000	A	A	A	A
MP3	162734	81099	1	0.000	A	A	A	A
MP3	162852	81158	1	0.000	A	A	A	A
MP3	162854	81159	1	0.000	A	A	A	A
MP3	162856	81160	1	0.000	A	A	A	A
MP3	162858	81161	1	0.000	A	A	A	A
MP3	162900	81182	1	0.000	A	A	A	A
MP3	162902	81183	1	0.000	A	A	A	A
MP3	162904	81184	1	0.000	A	A	A	A
MP3	162974	81219	1	0.000	A	A	A	A
MP3	163914	81689	1	0.000	A	A	A	A
MP3	163916	81690	1	0.000	A	A	A	A
MP3	163918	81691	1	0.000	A	A	A	A
MP3	163920	81692	1	0.000	A	A	A	A
MP3	163922	81693	1	0.000	A	A	A	A
MP3	163924	81694	1	0.000	A	A	A	A
MP3	163926	81695	1	0.000	A	A	A	A
MP3	163928	81696	1	0.000	A	A	A	A
MP3	164154	81809	1	0.000	A	A	A	A
MP3	164348	81906	1	0.000	A	A	A	A
MP3	164350	81907	1	0.000	A	A	A	A
MP3	164352	81908	1	0.000	A	A	A	A
MP3	164354	81909	1	0.000	A	A	A	A
MP3	164356	81910	1	0.000	A	A	A	A
MP3	164358	81911	1	0.000	A	A	A	A
MP3	164360	81912	1	0.000	A	A	A	A
MP3	164362	81913	1	0.000	A	A	A	A
MP3	164364	81914	1	0.000	A	A	A	A
MP3	164366	81915	1	0.000	A	A	A	A
MP3	164368	81916	1	0.000	A	A	A	A
MP3	164370	81917	1	0.000	A	A	A	A
WAV	162715	81089	2	0.300	A	A	A	A
WAV	162717	81090	2	0.300	A	A	A	A
WAV	162719	81091	2	0.300	A	A	A	A
WAV	162721	81092	2	0.300	A	A	A	A
WAV	162723	81093	2	0.300	A	A	A	A
WAV	162725	81094	2	0.300	A	A	A	A
WAV	162727	81095	2	0.300	A	A	A	A
WAV	162729	81096	2	0.300	A	A	A	A
WAV	162731	81097	2	0.300	A	A	A	A
WAV	162733	81098	2	0.300	A	A	A	A
WAV	162735	81099	2	0.300	A	A	A	A
WAV	162853	81158	2	0.300	A	A	A	A
WAV	162855	81159	2	0.300	A	A	A	A
WAV	162857	81160	2	0.300	A	A	A	A
WAV	162859	81161	2	0.300	A	A	A	A
WAV	162901	81182	2	0.300	A	A	A	A
WAV	162903	81183	2	0.300	A	A	A	A
WAV	162905	81184	2	0.300	A	A	A	A
WAV	162975	81219	2	0.300	A	A	A	A
WAV	163915	81689	2	0.300	A	A	A	A
WAV	163917	81690	2	0.300	A	A	A	A
WAV	163919	81691	2	0.300	A	A	A	A
WAV	163921	81692	2	0.300	A	A	A	A
WAV	163923	81693	2	0.300	A	A	A	A
WAV	163925	81694	2	0.300	A	A	A	A
WAV	163927	81695	2	0.300	A	A	A	A
WAV	163929	81696	2	0.300	A	A	A	A
WAV	164155	81809	2	0.300	A	A	A	A
WAV	164349	81906	2	0.300	A	A	A	A
WAV	164351	81907	2	0.300	A	A	A	A
WAV	164353	81908	2	0.300	A	A	A	A
WAV	164355	81909	2	0.300	A	A	A	A
WAV	164357	81910	2	0.300	A	A	A	A
WAV	164359	81911	2	0.300	A	A	A	A
WAV	164361	81912	2	0.300	A	A	A	A
WAV	164363	81913	2	0.300	A	A	A	A
WAV	164365	81914	2	0.300	A	A	A	A
WAV	164367	81915	2	0.300	A	A	A	A
WAV	164369	81916	2	0.300	A	A	A	A
WAV	164371	81917	2	0.300	A	A	A	A