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 (
    10676, 10675, 10674, 10673, 10639, 10640, 
    36830, 36829, 36828, 36827, 10596, 
    10595, 10591, 10565, 10562, 10561, 
    10560, 10594, 10556, 10515, 10513, 
    10512, 10505, 10500, 10492, 10484, 
    10482, 10479, 10474, 10490, 10489, 
    10488, 10487, 10486, 10485, 10483, 
    10481, 10480, 10478, 10477
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00107

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		80	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	20947	10474	1	0.000	A	A	A	A
MP3	20953	10477	1	0.000	A	A	A	A
MP3	20955	10478	1	0.000	A	A	A	A
MP3	20957	10479	1	0.000	A	A	A	A
MP3	20959	10480	1	0.000	A	A	A	A
MP3	20961	10481	1	0.000	A	A	A	A
MP3	20963	10482	1	0.000	A	A	A	A
MP3	20965	10483	1	0.000	A	A	A	A
MP3	20967	10484	1	0.000	A	A	A	A
MP3	20969	10485	1	0.000	A	A	A	A
MP3	20971	10486	1	0.000	A	A	A	A
MP3	20973	10487	1	0.000	A	A	A	A
MP3	20975	10488	1	0.000	A	A	A	A
MP3	20977	10489	1	0.000	A	A	A	A
MP3	20979	10490	1	0.000	A	A	A	A
MP3	20983	10492	1	0.000	A	A	A	A
MP3	20999	10500	1	0.000	A	A	A	A
MP3	21009	10505	1	0.000	A	A	A	A
MP3	21023	10512	1	0.000	A	A	A	A
MP3	21025	10513	1	0.000	A	A	A	A
MP3	21029	10515	1	0.000	A	A	A	A
MP3	21111	10556	1	0.000	A	A	A	A
MP3	21119	10560	1	0.000	A	A	A	A
MP3	21121	10561	1	0.000	A	A	A	A
MP3	21123	10562	1	0.000	A	A	A	A
MP3	21129	10565	1	0.000	A	A	A	A
MP3	21181	10591	1	0.000	A	A	A	A
MP3	21187	10594	1	0.000	A	A	A	A
MP3	21189	10595	1	0.000	A	A	A	A
MP3	21191	10596	1	0.000	A	A	A	A
MP3	21277	10639	1	0.000	A	A	A	A
MP3	21279	10640	1	0.000	A	A	A	A
MP3	21345	10673	1	0.000	A	A	A	A
MP3	21347	10674	1	0.000	A	A	A	A
MP3	21349	10675	1	0.000	A	A	A	A
MP3	21351	10676	1	0.000	A	A	A	A
MP3	73720	36827	1	0.000	A	A	A	A
MP3	73722	36828	1	0.000	A	A	A	A
MP3	73724	36829	1	0.000	A	A	A	A
MP3	73726	36830	1	0.000	A	A	A	A
WAV	20948	10474	2	0.300	A	A	A	A
WAV	20954	10477	2	0.300	A	A	A	A
WAV	20956	10478	2	0.300	A	A	A	A
WAV	20958	10479	2	0.300	A	A	A	A
WAV	20960	10480	2	0.300	A	A	A	A
WAV	20962	10481	2	0.300	A	A	A	A
WAV	20964	10482	2	0.300	A	A	A	A
WAV	20966	10483	2	0.300	A	A	A	A
WAV	20968	10484	2	0.300	A	A	A	A
WAV	20970	10485	2	0.300	A	A	A	A
WAV	20972	10486	2	0.300	A	A	A	A
WAV	20974	10487	2	0.300	A	A	A	A
WAV	20976	10488	2	0.300	A	A	A	A
WAV	20978	10489	2	0.300	A	A	A	A
WAV	20980	10490	2	0.300	A	A	A	A
WAV	20984	10492	2	0.300	A	A	A	A
WAV	21000	10500	2	0.300	A	A	A	A
WAV	21010	10505	2	0.300	A	A	A	A
WAV	21024	10512	2	0.300	A	A	A	A
WAV	21026	10513	2	0.300	A	A	A	A
WAV	21030	10515	2	0.300	A	A	A	A
WAV	21112	10556	2	0.300	A	A	A	A
WAV	21120	10560	2	0.300	A	A	A	A
WAV	21122	10561	2	0.300	A	A	A	A
WAV	21124	10562	2	0.300	A	A	A	A
WAV	21130	10565	2	0.300	A	A	A	A
WAV	21182	10591	2	0.300	A	A	A	A
WAV	21188	10594	2	0.300	A	A	A	A
WAV	21190	10595	2	0.300	A	A	A	A
WAV	21192	10596	2	0.300	A	A	A	A
WAV	21278	10639	2	0.300	A	A	A	A
WAV	21280	10640	2	0.300	A	A	A	A
WAV	21346	10673	2	0.300	A	A	A	A
WAV	21348	10674	2	0.300	A	A	A	A
WAV	21350	10675	2	0.300	A	A	A	A
WAV	21352	10676	2	0.300	A	A	A	A
WAV	73721	36827	2	0.300	A	A	A	A
WAV	73723	36828	2	0.300	A	A	A	A
WAV	73725	36829	2	0.300	A	A	A	A
WAV	73727	36830	2	0.300	A	A	A	A