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 (
    87258, 87257, 87256, 87255, 87254, 87252, 
    87251, 87249, 87247, 87246, 87245, 
    87244, 87243, 87242, 87241, 87239, 
    87238, 87237, 87236, 87235
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00099

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	175006	87235	1	0.000	A	A	A	A
MP3	175008	87236	1	0.000	A	A	A	A
MP3	175010	87237	1	0.000	A	A	A	A
MP3	175012	87238	1	0.000	A	A	A	A
MP3	175014	87239	1	0.000	A	A	A	A
MP3	175018	87241	1	0.000	A	A	A	A
MP3	175020	87242	1	0.000	A	A	A	A
MP3	175022	87243	1	0.000	A	A	A	A
MP3	175024	87244	1	0.000	A	A	A	A
MP3	175026	87245	1	0.000	A	A	A	A
MP3	175028	87246	1	0.000	A	A	A	A
MP3	175030	87247	1	0.000	A	A	A	A
MP3	175034	87249	1	0.000	A	A	A	A
MP3	175038	87251	1	0.000	A	A	A	A
MP3	175040	87252	1	0.000	A	A	A	A
MP3	175044	87254	1	0.000	A	A	A	A
MP3	175046	87255	1	0.000	A	A	A	A
MP3	175048	87256	1	0.000	A	A	A	A
MP3	175050	87257	1	0.000	A	A	A	A
MP3	175052	87258	1	0.000	A	A	A	A
WAV	175007	87235	2	0.300	A	A	A	A
WAV	175009	87236	2	0.300	A	A	A	A
WAV	175011	87237	2	0.300	A	A	A	A
WAV	175013	87238	2	0.300	A	A	A	A
WAV	175015	87239	2	0.300	A	A	A	A
WAV	175019	87241	2	0.300	A	A	A	A
WAV	175021	87242	2	0.300	A	A	A	A
WAV	175023	87243	2	0.300	A	A	A	A
WAV	175025	87244	2	0.300	A	A	A	A
WAV	175027	87245	2	0.300	A	A	A	A
WAV	175029	87246	2	0.300	A	A	A	A
WAV	175031	87247	2	0.300	A	A	A	A
WAV	175035	87249	2	0.300	A	A	A	A
WAV	175039	87251	2	0.300	A	A	A	A
WAV	175041	87252	2	0.300	A	A	A	A
WAV	175045	87254	2	0.300	A	A	A	A
WAV	175047	87255	2	0.300	A	A	A	A
WAV	175049	87256	2	0.300	A	A	A	A
WAV	175051	87257	2	0.300	A	A	A	A
WAV	175053	87258	2	0.300	A	A	A	A