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 (
    118807, 118908, 118544, 118522, 118934, 
    118393, 118581, 118260, 118258, 118448, 
    118428, 118279, 118588, 118587, 118487, 
    118241, 117944, 118121, 117951, 117949, 
    118318, 117791, 117788, 117664, 117957, 
    117781, 117780, 117522, 117923, 117749
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00127

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		60	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	235580	117522	1	0.000	A	A	A	A
MP3	235864	117664	1	0.000	A	A	A	A
MP3	236034	117749	1	0.000	A	A	A	A
MP3	236096	117780	1	0.000	A	A	A	A
MP3	236098	117781	1	0.000	A	A	A	A
MP3	236112	117788	1	0.000	A	A	A	A
MP3	236118	117791	1	0.000	A	A	A	A
MP3	236382	117923	1	0.000	A	A	A	A
MP3	236424	117944	1	0.000	A	A	A	A
MP3	236434	117949	1	0.000	A	A	A	A
MP3	236438	117951	1	0.000	A	A	A	A
MP3	236450	117957	1	0.000	A	A	A	A
MP3	236778	118121	1	0.000	A	A	A	A
MP3	237018	118241	1	0.000	A	A	A	A
MP3	237052	118258	1	0.000	A	A	A	A
MP3	237056	118260	1	0.000	A	A	A	A
MP3	237094	118279	1	0.000	A	A	A	A
MP3	237172	118318	1	0.000	A	A	A	A
MP3	237322	118393	1	0.000	A	A	A	A
MP3	237392	118428	1	0.000	A	A	A	A
MP3	237432	118448	1	0.000	A	A	A	A
MP3	237510	118487	1	0.000	A	A	A	A
MP3	237580	118522	1	0.000	A	A	A	A
MP3	237624	118544	1	0.000	A	A	A	A
MP3	237698	118581	1	0.000	A	A	A	A
MP3	237710	118587	1	0.000	A	A	A	A
MP3	237712	118588	1	0.000	A	A	A	A
MP3	238150	118807	1	0.000	A	A	A	A
MP3	238352	118908	1	0.000	A	A	A	A
MP3	238404	118934	1	0.000	A	A	A	A
WAV	235581	117522	2	0.300	A	A	A	A
WAV	235865	117664	2	0.300	A	A	A	A
WAV	236035	117749	2	0.300	A	A	A	A
WAV	236097	117780	2	0.300	A	A	A	A
WAV	236099	117781	2	0.300	A	A	A	A
WAV	236113	117788	2	0.300	A	A	A	A
WAV	236119	117791	2	0.300	A	A	A	A
WAV	236383	117923	2	0.300	A	A	A	A
WAV	236425	117944	2	0.300	A	A	A	A
WAV	236435	117949	2	0.300	A	A	A	A
WAV	236439	117951	2	0.300	A	A	A	A
WAV	236451	117957	2	0.300	A	A	A	A
WAV	236779	118121	2	0.300	A	A	A	A
WAV	237019	118241	2	0.300	A	A	A	A
WAV	237053	118258	2	0.300	A	A	A	A
WAV	237057	118260	2	0.300	A	A	A	A
WAV	237095	118279	2	0.300	A	A	A	A
WAV	237173	118318	2	0.300	A	A	A	A
WAV	237323	118393	2	0.300	A	A	A	A
WAV	237393	118428	2	0.300	A	A	A	A
WAV	237433	118448	2	0.300	A	A	A	A
WAV	237511	118487	2	0.300	A	A	A	A
WAV	237581	118522	2	0.300	A	A	A	A
WAV	237625	118544	2	0.300	A	A	A	A
WAV	237699	118581	2	0.300	A	A	A	A
WAV	237711	118587	2	0.300	A	A	A	A
WAV	237713	118588	2	0.300	A	A	A	A
WAV	238151	118807	2	0.300	A	A	A	A
WAV	238353	118908	2	0.300	A	A	A	A
WAV	238405	118934	2	0.300	A	A	A	A