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 (
    83114, 83067, 83051, 83987, 83832, 83165, 
    83136, 83135, 83134, 83116, 83115, 
    83831, 83830, 83654, 83653, 83652, 
    83633, 83632, 83631, 83630, 83629, 
    83628, 83627, 83626, 83625, 83624, 
    83623, 83593, 83592, 83591, 83590, 
    83589, 83588, 83587, 83486, 83485, 
    83476, 83475, 83474, 83473
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00143

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	166638	83051	1	0.000	A	A	A	A
MP3	166670	83067	1	0.000	A	A	A	A
MP3	166764	83114	1	0.000	A	A	A	A
MP3	166766	83115	1	0.000	A	A	A	A
MP3	166768	83116	1	0.000	A	A	A	A
MP3	166804	83134	1	0.000	A	A	A	A
MP3	166806	83135	1	0.000	A	A	A	A
MP3	166808	83136	1	0.000	A	A	A	A
MP3	166866	83165	1	0.000	A	A	A	A
MP3	167482	83473	1	0.000	A	A	A	A
MP3	167484	83474	1	0.000	A	A	A	A
MP3	167486	83475	1	0.000	A	A	A	A
MP3	167488	83476	1	0.000	A	A	A	A
MP3	167506	83485	1	0.000	A	A	A	A
MP3	167508	83486	1	0.000	A	A	A	A
MP3	167710	83587	1	0.000	A	A	A	A
MP3	167712	83588	1	0.000	A	A	A	A
MP3	167714	83589	1	0.000	A	A	A	A
MP3	167716	83590	1	0.000	A	A	A	A
MP3	167718	83591	1	0.000	A	A	A	A
MP3	167720	83592	1	0.000	A	A	A	A
MP3	167722	83593	1	0.000	A	A	A	A
MP3	167782	83623	1	0.000	A	A	A	A
MP3	167784	83624	1	0.000	A	A	A	A
MP3	167786	83625	1	0.000	A	A	A	A
MP3	167788	83626	1	0.000	A	A	A	A
MP3	167790	83627	1	0.000	A	A	A	A
MP3	167792	83628	1	0.000	A	A	A	A
MP3	167794	83629	1	0.000	A	A	A	A
MP3	167796	83630	1	0.000	A	A	A	A
MP3	167798	83631	1	0.000	A	A	A	A
MP3	167800	83632	1	0.000	A	A	A	A
MP3	167802	83633	1	0.000	A	A	A	A
MP3	167840	83652	1	0.000	A	A	A	A
MP3	167842	83653	1	0.000	A	A	A	A
MP3	167844	83654	1	0.000	A	A	A	A
MP3	168196	83830	1	0.000	A	A	A	A
MP3	168198	83831	1	0.000	A	A	A	A
MP3	168200	83832	1	0.000	A	A	A	A
MP3	168510	83987	1	0.000	A	A	A	A
WAV	166639	83051	2	0.300	A	A	A	A
WAV	166671	83067	2	0.300	A	A	A	A
WAV	166765	83114	2	0.300	A	A	A	A
WAV	166767	83115	2	0.300	A	A	A	A
WAV	166769	83116	2	0.300	A	A	A	A
WAV	166805	83134	2	0.300	A	A	A	A
WAV	166807	83135	2	0.300	A	A	A	A
WAV	166809	83136	2	0.300	A	A	A	A
WAV	166867	83165	2	0.300	A	A	A	A
WAV	167483	83473	2	0.300	A	A	A	A
WAV	167485	83474	2	0.300	A	A	A	A
WAV	167487	83475	2	0.300	A	A	A	A
WAV	167489	83476	2	0.300	A	A	A	A
WAV	167507	83485	2	0.300	A	A	A	A
WAV	167509	83486	2	0.300	A	A	A	A
WAV	167711	83587	2	0.300	A	A	A	A
WAV	167713	83588	2	0.300	A	A	A	A
WAV	167715	83589	2	0.300	A	A	A	A
WAV	167717	83590	2	0.300	A	A	A	A
WAV	167719	83591	2	0.300	A	A	A	A
WAV	167721	83592	2	0.300	A	A	A	A
WAV	167723	83593	2	0.300	A	A	A	A
WAV	167783	83623	2	0.300	A	A	A	A
WAV	167785	83624	2	0.300	A	A	A	A
WAV	167787	83625	2	0.300	A	A	A	A
WAV	167789	83626	2	0.300	A	A	A	A
WAV	167791	83627	2	0.300	A	A	A	A
WAV	167793	83628	2	0.300	A	A	A	A
WAV	167795	83629	2	0.300	A	A	A	A
WAV	167797	83630	2	0.300	A	A	A	A
WAV	167799	83631	2	0.300	A	A	A	A
WAV	167801	83632	2	0.300	A	A	A	A
WAV	167803	83633	2	0.300	A	A	A	A
WAV	167841	83652	2	0.300	A	A	A	A
WAV	167843	83653	2	0.300	A	A	A	A
WAV	167845	83654	2	0.300	A	A	A	A
WAV	168197	83830	2	0.300	A	A	A	A
WAV	168199	83831	2	0.300	A	A	A	A
WAV	168201	83832	2	0.300	A	A	A	A
WAV	168511	83987	2	0.300	A	A	A	A