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 (
    82346, 82345, 82367, 82366, 82329, 82466, 
    82465, 82359, 82435, 82434, 82433, 
    82432, 82362, 82355, 82265, 82559, 
    82719, 82718, 82717, 82716, 82715, 
    82547, 82443, 82442, 82324, 82270, 
    82269, 82798, 82797, 82796, 82795, 
    82740, 82739, 82738, 82727, 82726, 
    82725, 82163, 82162, 82168
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00122

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	164860	82162	1	0.000	A	A	A	A
MP3	164862	82163	1	0.000	A	A	A	A
MP3	164872	82168	1	0.000	A	A	A	A
MP3	165066	82265	1	0.000	A	A	A	A
MP3	165074	82269	1	0.000	A	A	A	A
MP3	165076	82270	1	0.000	A	A	A	A
MP3	165184	82324	1	0.000	A	A	A	A
MP3	165194	82329	1	0.000	A	A	A	A
MP3	165226	82345	1	0.000	A	A	A	A
MP3	165228	82346	1	0.000	A	A	A	A
MP3	165246	82355	1	0.000	A	A	A	A
MP3	165254	82359	1	0.000	A	A	A	A
MP3	165260	82362	1	0.000	A	A	A	A
MP3	165268	82366	1	0.000	A	A	A	A
MP3	165270	82367	1	0.000	A	A	A	A
MP3	165400	82432	1	0.000	A	A	A	A
MP3	165402	82433	1	0.000	A	A	A	A
MP3	165404	82434	1	0.000	A	A	A	A
MP3	165406	82435	1	0.000	A	A	A	A
MP3	165420	82442	1	0.000	A	A	A	A
MP3	165422	82443	1	0.000	A	A	A	A
MP3	165466	82465	1	0.000	A	A	A	A
MP3	165468	82466	1	0.000	A	A	A	A
MP3	165630	82547	1	0.000	A	A	A	A
MP3	165654	82559	1	0.000	A	A	A	A
MP3	165966	82715	1	0.000	A	A	A	A
MP3	165968	82716	1	0.000	A	A	A	A
MP3	165970	82717	1	0.000	A	A	A	A
MP3	165972	82718	1	0.000	A	A	A	A
MP3	165974	82719	1	0.000	A	A	A	A
MP3	165986	82725	1	0.000	A	A	A	A
MP3	165988	82726	1	0.000	A	A	A	A
MP3	165990	82727	1	0.000	A	A	A	A
MP3	166012	82738	1	0.000	A	A	A	A
MP3	166014	82739	1	0.000	A	A	A	A
MP3	166016	82740	1	0.000	A	A	A	A
MP3	166126	82795	1	0.000	A	A	A	A
MP3	166128	82796	1	0.000	A	A	A	A
MP3	166130	82797	1	0.000	A	A	A	A
MP3	166132	82798	1	0.000	A	A	A	A
WAV	164861	82162	2	0.300	A	A	A	A
WAV	164863	82163	2	0.300	A	A	A	A
WAV	164873	82168	2	0.300	A	A	A	A
WAV	165067	82265	2	0.300	A	A	A	A
WAV	165075	82269	2	0.300	A	A	A	A
WAV	165077	82270	2	0.300	A	A	A	A
WAV	165185	82324	2	0.300	A	A	A	A
WAV	165195	82329	2	0.300	A	A	A	A
WAV	165227	82345	2	0.300	A	A	A	A
WAV	165229	82346	2	0.300	A	A	A	A
WAV	165247	82355	2	0.300	A	A	A	A
WAV	165255	82359	2	0.300	A	A	A	A
WAV	165261	82362	2	0.300	A	A	A	A
WAV	165269	82366	2	0.300	A	A	A	A
WAV	165271	82367	2	0.300	A	A	A	A
WAV	165401	82432	2	0.300	A	A	A	A
WAV	165403	82433	2	0.300	A	A	A	A
WAV	165405	82434	2	0.300	A	A	A	A
WAV	165407	82435	2	0.300	A	A	A	A
WAV	165421	82442	2	0.300	A	A	A	A
WAV	165423	82443	2	0.300	A	A	A	A
WAV	165467	82465	2	0.300	A	A	A	A
WAV	165469	82466	2	0.300	A	A	A	A
WAV	165631	82547	2	0.300	A	A	A	A
WAV	165655	82559	2	0.300	A	A	A	A
WAV	165967	82715	2	0.300	A	A	A	A
WAV	165969	82716	2	0.300	A	A	A	A
WAV	165971	82717	2	0.300	A	A	A	A
WAV	165973	82718	2	0.300	A	A	A	A
WAV	165975	82719	2	0.300	A	A	A	A
WAV	165987	82725	2	0.300	A	A	A	A
WAV	165989	82726	2	0.300	A	A	A	A
WAV	165991	82727	2	0.300	A	A	A	A
WAV	166013	82738	2	0.300	A	A	A	A
WAV	166015	82739	2	0.300	A	A	A	A
WAV	166017	82740	2	0.300	A	A	A	A
WAV	166127	82795	2	0.300	A	A	A	A
WAV	166129	82796	2	0.300	A	A	A	A
WAV	166131	82797	2	0.300	A	A	A	A
WAV	166133	82798	2	0.300	A	A	A	A