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 (
    118593, 118592, 118590, 118577, 119080, 
    118939, 118482, 118481, 118477, 118999, 
    118997, 118440, 118943, 118331, 118320, 
    118319, 118861, 118860, 118421, 118197, 
    118195, 118043, 118017, 117835, 118201, 
    118118, 117778, 117713, 117699, 117697
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00124

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	235930	117697	1	0.000	A	A	A	A
MP3	235934	117699	1	0.000	A	A	A	A
MP3	235962	117713	1	0.000	A	A	A	A
MP3	236092	117778	1	0.000	A	A	A	A
MP3	236206	117835	1	0.000	A	A	A	A
MP3	236570	118017	1	0.000	A	A	A	A
MP3	236622	118043	1	0.000	A	A	A	A
MP3	236772	118118	1	0.000	A	A	A	A
MP3	236926	118195	1	0.000	A	A	A	A
MP3	236930	118197	1	0.000	A	A	A	A
MP3	236938	118201	1	0.000	A	A	A	A
MP3	237174	118319	1	0.000	A	A	A	A
MP3	237176	118320	1	0.000	A	A	A	A
MP3	237198	118331	1	0.000	A	A	A	A
MP3	237378	118421	1	0.000	A	A	A	A
MP3	237416	118440	1	0.000	A	A	A	A
MP3	237490	118477	1	0.000	A	A	A	A
MP3	237498	118481	1	0.000	A	A	A	A
MP3	237500	118482	1	0.000	A	A	A	A
MP3	237690	118577	1	0.000	A	A	A	A
MP3	237716	118590	1	0.000	A	A	A	A
MP3	237720	118592	1	0.000	A	A	A	A
MP3	237722	118593	1	0.000	A	A	A	A
MP3	238256	118860	1	0.000	A	A	A	A
MP3	238258	118861	1	0.000	A	A	A	A
MP3	238414	118939	1	0.000	A	A	A	A
MP3	238422	118943	1	0.000	A	A	A	A
MP3	238530	118997	1	0.000	A	A	A	A
MP3	238534	118999	1	0.000	A	A	A	A
MP3	238696	119080	1	0.000	A	A	A	A
WAV	235931	117697	2	0.300	A	A	A	A
WAV	235935	117699	2	0.300	A	A	A	A
WAV	235963	117713	2	0.300	A	A	A	A
WAV	236093	117778	2	0.300	A	A	A	A
WAV	236207	117835	2	0.300	A	A	A	A
WAV	236571	118017	2	0.300	A	A	A	A
WAV	236623	118043	2	0.300	A	A	A	A
WAV	236773	118118	2	0.300	A	A	A	A
WAV	236927	118195	2	0.300	A	A	A	A
WAV	236931	118197	2	0.300	A	A	A	A
WAV	236939	118201	2	0.300	A	A	A	A
WAV	237175	118319	2	0.300	A	A	A	A
WAV	237177	118320	2	0.300	A	A	A	A
WAV	237199	118331	2	0.300	A	A	A	A
WAV	237379	118421	2	0.300	A	A	A	A
WAV	237417	118440	2	0.300	A	A	A	A
WAV	237491	118477	2	0.300	A	A	A	A
WAV	237499	118481	2	0.300	A	A	A	A
WAV	237501	118482	2	0.300	A	A	A	A
WAV	237691	118577	2	0.300	A	A	A	A
WAV	237717	118590	2	0.300	A	A	A	A
WAV	237721	118592	2	0.300	A	A	A	A
WAV	237723	118593	2	0.300	A	A	A	A
WAV	238257	118860	2	0.300	A	A	A	A
WAV	238259	118861	2	0.300	A	A	A	A
WAV	238415	118939	2	0.300	A	A	A	A
WAV	238423	118943	2	0.300	A	A	A	A
WAV	238531	118997	2	0.300	A	A	A	A
WAV	238535	118999	2	0.300	A	A	A	A
WAV	238697	119080	2	0.300	A	A	A	A