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 (
    86375, 87209, 83856, 87333, 87295, 87294, 
    87293, 87286, 87285, 87283, 86798, 
    87104, 87056, 86822, 86801, 86779, 
    87202, 87103, 87057, 86993
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00106

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	168248	83856	1	0.000	A	A	A	A
MP3	173286	86375	1	0.000	A	A	A	A
MP3	174094	86779	1	0.000	A	A	A	A
MP3	174132	86798	1	0.000	A	A	A	A
MP3	174138	86801	1	0.000	A	A	A	A
MP3	174180	86822	1	0.000	A	A	A	A
MP3	174522	86993	1	0.000	A	A	A	A
MP3	174648	87056	1	0.000	A	A	A	A
MP3	174650	87057	1	0.000	A	A	A	A
MP3	174742	87103	1	0.000	A	A	A	A
MP3	174744	87104	1	0.000	A	A	A	A
MP3	174940	87202	1	0.000	A	A	A	A
MP3	174954	87209	1	0.000	A	A	A	A
MP3	175102	87283	1	0.000	A	A	A	A
MP3	175106	87285	1	0.000	A	A	A	A
MP3	175108	87286	1	0.000	A	A	A	A
MP3	175122	87293	1	0.000	A	A	A	A
MP3	175124	87294	1	0.000	A	A	A	A
MP3	175126	87295	1	0.000	A	A	A	A
MP3	175202	87333	1	0.000	A	A	A	A
WAV	168249	83856	2	0.300	A	A	A	A
WAV	173287	86375	2	0.300	A	A	A	A
WAV	174095	86779	2	0.300	A	A	A	A
WAV	174133	86798	2	0.300	A	A	A	A
WAV	174139	86801	2	0.300	A	A	A	A
WAV	174181	86822	2	0.300	A	A	A	A
WAV	174523	86993	2	0.300	A	A	A	A
WAV	174649	87056	2	0.300	A	A	A	A
WAV	174651	87057	2	0.300	A	A	A	A
WAV	174743	87103	2	0.300	A	A	A	A
WAV	174745	87104	2	0.300	A	A	A	A
WAV	174941	87202	2	0.300	A	A	A	A
WAV	174955	87209	2	0.300	A	A	A	A
WAV	175103	87283	2	0.300	A	A	A	A
WAV	175107	87285	2	0.300	A	A	A	A
WAV	175109	87286	2	0.300	A	A	A	A
WAV	175123	87293	2	0.300	A	A	A	A
WAV	175125	87294	2	0.300	A	A	A	A
WAV	175127	87295	2	0.300	A	A	A	A
WAV	175203	87333	2	0.300	A	A	A	A