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 (
    34769, 34761, 34646, 34854, 34742, 34603, 
    34594, 34443, 34442, 34257, 34315, 
    33975, 33974, 34093, 34232, 33901, 
    33945, 33670, 33660, 33774
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00082

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		40	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	67386	33660	1	0.000	A	A	A	A
MP3	67406	33670	1	0.000	A	A	A	A
MP3	67614	33774	1	0.000	A	A	A	A
MP3	67868	33901	1	0.000	A	A	A	A
MP3	67956	33945	1	0.000	A	A	A	A
MP3	68014	33974	1	0.000	A	A	A	A
MP3	68016	33975	1	0.000	A	A	A	A
MP3	68252	34093	1	0.000	A	A	A	A
MP3	68530	34232	1	0.000	A	A	A	A
MP3	68580	34257	1	0.000	A	A	A	A
MP3	68696	34315	1	0.000	A	A	A	A
MP3	68950	34442	1	0.000	A	A	A	A
MP3	68952	34443	1	0.000	A	A	A	A
MP3	69254	34594	1	0.000	A	A	A	A
MP3	69272	34603	1	0.000	A	A	A	A
MP3	69358	34646	1	0.000	A	A	A	A
MP3	69550	34742	1	0.000	A	A	A	A
MP3	69588	34761	1	0.000	A	A	A	A
MP3	69604	34769	1	0.000	A	A	A	A
MP3	69774	34854	1	0.000	A	A	A	A
WAV	67387	33660	2	0.300	A	A	A	A
WAV	67407	33670	2	0.300	A	A	A	A
WAV	67615	33774	2	0.300	A	A	A	A
WAV	67869	33901	2	0.300	A	A	A	A
WAV	67957	33945	2	0.300	A	A	A	A
WAV	68015	33974	2	0.300	A	A	A	A
WAV	68017	33975	2	0.300	A	A	A	A
WAV	68253	34093	2	0.300	A	A	A	A
WAV	68531	34232	2	0.300	A	A	A	A
WAV	68581	34257	2	0.300	A	A	A	A
WAV	68697	34315	2	0.300	A	A	A	A
WAV	68951	34442	2	0.300	A	A	A	A
WAV	68953	34443	2	0.300	A	A	A	A
WAV	69255	34594	2	0.300	A	A	A	A
WAV	69273	34603	2	0.300	A	A	A	A
WAV	69359	34646	2	0.300	A	A	A	A
WAV	69551	34742	2	0.300	A	A	A	A
WAV	69589	34761	2	0.300	A	A	A	A
WAV	69605	34769	2	0.300	A	A	A	A
WAV	69775	34854	2	0.300	A	A	A	A