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 (
    118479, 118442, 118325, 118497, 118016, 
    117785, 117973, 117747, 117041, 117168, 
    116172, 115229, 114508, 113988, 113978, 
    113068, 112202, 112201, 111986, 111301, 
    111006, 110855, 110513, 110747, 109972, 
    109762, 108615, 108087, 107274, 106030
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00185

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	212596	106030	1	0.000	A	A	A	A
MP3	215084	107274	1	0.000	A	A	A	A
MP3	216710	108087	1	0.000	A	A	A	A
MP3	217766	108615	1	0.000	A	A	A	A
MP3	220060	109762	1	0.000	A	A	A	A
MP3	220480	109972	1	0.000	A	A	A	A
MP3	221562	110513	1	0.000	A	A	A	A
MP3	222030	110747	1	0.000	A	A	A	A
MP3	222246	110855	1	0.000	A	A	A	A
MP3	222548	111006	1	0.000	A	A	A	A
MP3	223138	111301	1	0.000	A	A	A	A
MP3	224508	111986	1	0.000	A	A	A	A
MP3	224938	112201	1	0.000	A	A	A	A
MP3	224940	112202	1	0.000	A	A	A	A
MP3	226672	113068	1	0.000	A	A	A	A
MP3	228492	113978	1	0.000	A	A	A	A
MP3	228512	113988	1	0.000	A	A	A	A
MP3	229552	114508	1	0.000	A	A	A	A
MP3	230994	115229	1	0.000	A	A	A	A
MP3	232880	116172	1	0.000	A	A	A	A
MP3	234618	117041	1	0.000	A	A	A	A
MP3	234872	117168	1	0.000	A	A	A	A
MP3	236030	117747	1	0.000	A	A	A	A
MP3	236106	117785	1	0.000	A	A	A	A
MP3	236482	117973	1	0.000	A	A	A	A
MP3	236568	118016	1	0.000	A	A	A	A
MP3	237186	118325	1	0.000	A	A	A	A
MP3	237420	118442	1	0.000	A	A	A	A
MP3	237494	118479	1	0.000	A	A	A	A
MP3	237530	118497	1	0.000	A	A	A	A
WAV	212597	106030	2	0.300	A	A	A	A
WAV	215085	107274	2	0.300	A	A	A	A
WAV	216711	108087	2	0.300	A	A	A	A
WAV	217767	108615	2	0.300	A	A	A	A
WAV	220061	109762	2	0.300	A	A	A	A
WAV	220481	109972	2	0.300	A	A	A	A
WAV	221563	110513	2	0.300	A	A	A	A
WAV	222031	110747	2	0.300	A	A	A	A
WAV	222247	110855	2	0.300	A	A	A	A
WAV	222549	111006	2	0.300	A	A	A	A
WAV	223139	111301	2	0.300	A	A	A	A
WAV	224509	111986	2	0.300	A	A	A	A
WAV	224939	112201	2	0.300	A	A	A	A
WAV	224941	112202	2	0.300	A	A	A	A
WAV	226673	113068	2	0.300	A	A	A	A
WAV	228493	113978	2	0.300	A	A	A	A
WAV	228513	113988	2	0.300	A	A	A	A
WAV	229553	114508	2	0.300	A	A	A	A
WAV	230995	115229	2	0.300	A	A	A	A
WAV	232881	116172	2	0.300	A	A	A	A
WAV	234619	117041	2	0.300	A	A	A	A
WAV	234873	117168	2	0.300	A	A	A	A
WAV	236031	117747	2	0.300	A	A	A	A
WAV	236107	117785	2	0.300	A	A	A	A
WAV	236483	117973	2	0.300	A	A	A	A
WAV	236569	118016	2	0.300	A	A	A	A
WAV	237187	118325	2	0.300	A	A	A	A
WAV	237421	118442	2	0.300	A	A	A	A
WAV	237495	118479	2	0.300	A	A	A	A
WAV	237531	118497	2	0.300	A	A	A	A