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 (
    83660, 83659, 83658, 83657, 83656, 83655, 
    83612, 84110, 84071, 83910, 83512, 
    83511, 84069, 84068, 84067, 84066, 
    84065, 83776, 83775, 83774, 83773, 
    83772, 83771, 83770, 83769, 83768, 
    83767, 83766, 83765, 83764, 83763, 
    83762, 83761, 83760, 83759, 83560, 
    83372, 83371, 83370, 83369
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00133

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	167274	83369	1	0.000	A	A	A	A
MP3	167276	83370	1	0.000	A	A	A	A
MP3	167278	83371	1	0.000	A	A	A	A
MP3	167280	83372	1	0.000	A	A	A	A
MP3	167558	83511	1	0.000	A	A	A	A
MP3	167560	83512	1	0.000	A	A	A	A
MP3	167656	83560	1	0.000	A	A	A	A
MP3	167760	83612	1	0.000	A	A	A	A
MP3	167846	83655	1	0.000	A	A	A	A
MP3	167848	83656	1	0.000	A	A	A	A
MP3	167850	83657	1	0.000	A	A	A	A
MP3	167852	83658	1	0.000	A	A	A	A
MP3	167854	83659	1	0.000	A	A	A	A
MP3	167856	83660	1	0.000	A	A	A	A
MP3	168054	83759	1	0.000	A	A	A	A
MP3	168056	83760	1	0.000	A	A	A	A
MP3	168058	83761	1	0.000	A	A	A	A
MP3	168060	83762	1	0.000	A	A	A	A
MP3	168062	83763	1	0.000	A	A	A	A
MP3	168064	83764	1	0.000	A	A	A	A
MP3	168066	83765	1	0.000	A	A	A	A
MP3	168068	83766	1	0.000	A	A	A	A
MP3	168070	83767	1	0.000	A	A	A	A
MP3	168072	83768	1	0.000	A	A	A	A
MP3	168074	83769	1	0.000	A	A	A	A
MP3	168076	83770	1	0.000	A	A	A	A
MP3	168078	83771	1	0.000	A	A	A	A
MP3	168080	83772	1	0.000	A	A	A	A
MP3	168082	83773	1	0.000	A	A	A	A
MP3	168084	83774	1	0.000	A	A	A	A
MP3	168086	83775	1	0.000	A	A	A	A
MP3	168088	83776	1	0.000	A	A	A	A
MP3	168356	83910	1	0.000	A	A	A	A
MP3	168666	84065	1	0.000	A	A	A	A
MP3	168668	84066	1	0.000	A	A	A	A
MP3	168670	84067	1	0.000	A	A	A	A
MP3	168672	84068	1	0.000	A	A	A	A
MP3	168674	84069	1	0.000	A	A	A	A
MP3	168678	84071	1	0.000	A	A	A	A
MP3	168756	84110	1	0.000	A	A	A	A
WAV	167275	83369	2	0.300	A	A	A	A
WAV	167277	83370	2	0.300	A	A	A	A
WAV	167279	83371	2	0.300	A	A	A	A
WAV	167281	83372	2	0.300	A	A	A	A
WAV	167559	83511	2	0.300	A	A	A	A
WAV	167561	83512	2	0.300	A	A	A	A
WAV	167657	83560	2	0.300	A	A	A	A
WAV	167761	83612	2	0.300	A	A	A	A
WAV	167847	83655	2	0.300	A	A	A	A
WAV	167849	83656	2	0.300	A	A	A	A
WAV	167851	83657	2	0.300	A	A	A	A
WAV	167853	83658	2	0.300	A	A	A	A
WAV	167855	83659	2	0.300	A	A	A	A
WAV	167857	83660	2	0.300	A	A	A	A
WAV	168055	83759	2	0.300	A	A	A	A
WAV	168057	83760	2	0.300	A	A	A	A
WAV	168059	83761	2	0.300	A	A	A	A
WAV	168061	83762	2	0.300	A	A	A	A
WAV	168063	83763	2	0.300	A	A	A	A
WAV	168065	83764	2	0.300	A	A	A	A
WAV	168067	83765	2	0.300	A	A	A	A
WAV	168069	83766	2	0.300	A	A	A	A
WAV	168071	83767	2	0.300	A	A	A	A
WAV	168073	83768	2	0.300	A	A	A	A
WAV	168075	83769	2	0.300	A	A	A	A
WAV	168077	83770	2	0.300	A	A	A	A
WAV	168079	83771	2	0.300	A	A	A	A
WAV	168081	83772	2	0.300	A	A	A	A
WAV	168083	83773	2	0.300	A	A	A	A
WAV	168085	83774	2	0.300	A	A	A	A
WAV	168087	83775	2	0.300	A	A	A	A
WAV	168089	83776	2	0.300	A	A	A	A
WAV	168357	83910	2	0.300	A	A	A	A
WAV	168667	84065	2	0.300	A	A	A	A
WAV	168669	84066	2	0.300	A	A	A	A
WAV	168671	84067	2	0.300	A	A	A	A
WAV	168673	84068	2	0.300	A	A	A	A
WAV	168675	84069	2	0.300	A	A	A	A
WAV	168679	84071	2	0.300	A	A	A	A
WAV	168757	84110	2	0.300	A	A	A	A