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 (
    120642, 119021, 119020, 119019, 119018, 
    119017, 119016, 119015, 119014, 119013, 
    119012, 119011, 119010, 119009, 119008, 
    119007, 119006, 119005, 119004, 119003
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00112

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	238542	119003	1	0.000	A	A	A	A
MP3	238544	119004	1	0.000	A	A	A	A
MP3	238546	119005	1	0.000	A	A	A	A
MP3	238548	119006	1	0.000	A	A	A	A
MP3	238550	119007	1	0.000	A	A	A	A
MP3	238552	119008	1	0.000	A	A	A	A
MP3	238554	119009	1	0.000	A	A	A	A
MP3	238556	119010	1	0.000	A	A	A	A
MP3	238558	119011	1	0.000	A	A	A	A
MP3	238560	119012	1	0.000	A	A	A	A
MP3	238562	119013	1	0.000	A	A	A	A
MP3	238564	119014	1	0.000	A	A	A	A
MP3	238566	119015	1	0.000	A	A	A	A
MP3	238568	119016	1	0.000	A	A	A	A
MP3	238570	119017	1	0.000	A	A	A	A
MP3	238572	119018	1	0.000	A	A	A	A
MP3	238574	119019	1	0.000	A	A	A	A
MP3	238576	119020	1	0.000	A	A	A	A
MP3	238578	119021	1	0.000	A	A	A	A
MP3	241820	120642	1	0.000	A	A	A	A
WAV	238543	119003	2	0.300	A	A	A	A
WAV	238545	119004	2	0.300	A	A	A	A
WAV	238547	119005	2	0.300	A	A	A	A
WAV	238549	119006	2	0.300	A	A	A	A
WAV	238551	119007	2	0.300	A	A	A	A
WAV	238553	119008	2	0.300	A	A	A	A
WAV	238555	119009	2	0.300	A	A	A	A
WAV	238557	119010	2	0.300	A	A	A	A
WAV	238559	119011	2	0.300	A	A	A	A
WAV	238561	119012	2	0.300	A	A	A	A
WAV	238563	119013	2	0.300	A	A	A	A
WAV	238565	119014	2	0.300	A	A	A	A
WAV	238567	119015	2	0.300	A	A	A	A
WAV	238569	119016	2	0.300	A	A	A	A
WAV	238571	119017	2	0.300	A	A	A	A
WAV	238573	119018	2	0.300	A	A	A	A
WAV	238575	119019	2	0.300	A	A	A	A
WAV	238577	119020	2	0.300	A	A	A	A
WAV	238579	119021	2	0.300	A	A	A	A
WAV	241821	120642	2	1.500	A	A	A	A