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 (
    81841, 81840, 81839, 81838, 82159, 82143, 
    81956, 81708, 81707, 81678, 81860, 
    82119, 81855, 81854, 82120, 81977, 
    81976, 81720, 81717, 81583, 82744, 
    82743, 82742, 82741, 81766, 81765, 
    81764, 81679, 81536, 81313, 81312, 
    81311, 82042, 81551, 81550, 81549, 
    81548, 81547, 81546, 81400
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00156

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	163158	81311	1	0.000	A	A	A	A
MP3	163160	81312	1	0.000	A	A	A	A
MP3	163162	81313	1	0.000	A	A	A	A
MP3	163336	81400	1	0.000	A	A	A	A
MP3	163608	81536	1	0.000	A	A	A	A
MP3	163628	81546	1	0.000	A	A	A	A
MP3	163630	81547	1	0.000	A	A	A	A
MP3	163632	81548	1	0.000	A	A	A	A
MP3	163634	81549	1	0.000	A	A	A	A
MP3	163636	81550	1	0.000	A	A	A	A
MP3	163638	81551	1	0.000	A	A	A	A
MP3	163702	81583	1	0.000	A	A	A	A
MP3	163892	81678	1	0.000	A	A	A	A
MP3	163894	81679	1	0.000	A	A	A	A
MP3	163950	81707	1	0.000	A	A	A	A
MP3	163952	81708	1	0.000	A	A	A	A
MP3	163970	81717	1	0.000	A	A	A	A
MP3	163976	81720	1	0.000	A	A	A	A
MP3	164064	81764	1	0.000	A	A	A	A
MP3	164066	81765	1	0.000	A	A	A	A
MP3	164068	81766	1	0.000	A	A	A	A
MP3	164212	81838	1	0.000	A	A	A	A
MP3	164214	81839	1	0.000	A	A	A	A
MP3	164216	81840	1	0.000	A	A	A	A
MP3	164218	81841	1	0.000	A	A	A	A
MP3	164244	81854	1	0.000	A	A	A	A
MP3	164246	81855	1	0.000	A	A	A	A
MP3	164256	81860	1	0.000	A	A	A	A
MP3	164448	81956	1	0.000	A	A	A	A
MP3	164488	81976	1	0.000	A	A	A	A
MP3	164490	81977	1	0.000	A	A	A	A
MP3	164620	82042	1	0.000	A	A	A	A
MP3	164774	82119	1	0.000	A	A	A	A
MP3	164776	82120	1	0.000	A	A	A	A
MP3	164822	82143	1	0.000	A	A	A	A
MP3	164854	82159	1	0.000	A	A	A	A
MP3	166018	82741	1	0.000	A	A	A	A
MP3	166020	82742	1	0.000	A	A	A	A
MP3	166022	82743	1	0.000	A	A	A	A
MP3	166024	82744	1	0.000	A	A	A	A
WAV	163159	81311	2	0.300	A	A	A	A
WAV	163161	81312	2	0.300	A	A	A	A
WAV	163163	81313	2	0.300	A	A	A	A
WAV	163337	81400	2	0.300	A	A	A	A
WAV	163609	81536	2	0.300	A	A	A	A
WAV	163629	81546	2	0.300	A	A	A	A
WAV	163631	81547	2	0.300	A	A	A	A
WAV	163633	81548	2	0.300	A	A	A	A
WAV	163635	81549	2	0.300	A	A	A	A
WAV	163637	81550	2	0.300	A	A	A	A
WAV	163639	81551	2	0.300	A	A	A	A
WAV	163703	81583	2	0.300	A	A	A	A
WAV	163893	81678	2	0.300	A	A	A	A
WAV	163895	81679	2	0.300	A	A	A	A
WAV	163951	81707	2	0.300	A	A	A	A
WAV	163953	81708	2	0.300	A	A	A	A
WAV	163971	81717	2	0.300	A	A	A	A
WAV	163977	81720	2	0.300	A	A	A	A
WAV	164065	81764	2	0.300	A	A	A	A
WAV	164067	81765	2	0.300	A	A	A	A
WAV	164069	81766	2	0.300	A	A	A	A
WAV	164213	81838	2	0.300	A	A	A	A
WAV	164215	81839	2	0.300	A	A	A	A
WAV	164217	81840	2	0.300	A	A	A	A
WAV	164219	81841	2	0.300	A	A	A	A
WAV	164245	81854	2	0.300	A	A	A	A
WAV	164247	81855	2	0.300	A	A	A	A
WAV	164257	81860	2	0.300	A	A	A	A
WAV	164449	81956	2	0.300	A	A	A	A
WAV	164489	81976	2	0.300	A	A	A	A
WAV	164491	81977	2	0.300	A	A	A	A
WAV	164621	82042	2	0.300	A	A	A	A
WAV	164775	82119	2	0.300	A	A	A	A
WAV	164777	82120	2	0.300	A	A	A	A
WAV	164823	82143	2	0.300	A	A	A	A
WAV	164855	82159	2	0.300	A	A	A	A
WAV	166019	82741	2	0.300	A	A	A	A
WAV	166021	82742	2	0.300	A	A	A	A
WAV	166023	82743	2	0.300	A	A	A	A
WAV	166025	82744	2	0.300	A	A	A	A