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 (
    66414, 66228, 65521, 66034, 66391, 66372, 
    66371, 66370, 66369, 66138, 66342, 
    66292, 66331, 66210, 65770, 66326, 
    66230, 66050, 66227, 66047, 66278, 
    66276, 66275, 66226, 65774, 65773, 
    65355, 66033, 66037, 66170, 66165, 
    65769, 65596, 66101, 66100, 66049, 
    66048, 65767, 65461, 65460
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00123

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	130936	65355	1	0.000	A	A	A	A
MP3	131186	65460	1	0.000	A	A	A	A
MP3	131188	65461	1	0.000	A	A	A	A
MP3	131308	65521	1	0.000	A	A	A	A
MP3	131626	65596	1	0.000	A	A	A	A
MP3	131968	65767	1	0.000	A	A	A	A
MP3	131972	65769	1	0.000	A	A	A	A
MP3	131974	65770	1	0.000	A	A	A	A
MP3	131980	65773	1	0.000	A	A	A	A
MP3	131982	65774	1	0.000	A	A	A	A
MP3	132524	66033	1	0.000	A	A	A	A
MP3	132526	66034	1	0.000	A	A	A	A
MP3	132532	66037	1	0.000	A	A	A	A
MP3	132552	66047	1	0.000	A	A	A	A
MP3	132554	66048	1	0.000	A	A	A	A
MP3	132556	66049	1	0.000	A	A	A	A
MP3	132558	66050	1	0.000	A	A	A	A
MP3	132658	66100	1	0.000	A	A	A	A
MP3	132660	66101	1	0.000	A	A	A	A
MP3	132734	66138	1	0.000	A	A	A	A
MP3	132788	66165	1	0.000	A	A	A	A
MP3	132798	66170	1	0.000	A	A	A	A
MP3	132878	66210	1	0.000	A	A	A	A
MP3	132910	66226	1	0.000	A	A	A	A
MP3	132912	66227	1	0.000	A	A	A	A
MP3	132914	66228	1	0.000	A	A	A	A
MP3	132918	66230	1	0.000	A	A	A	A
MP3	133008	66275	1	0.000	A	A	A	A
MP3	133010	66276	1	0.000	A	A	A	A
MP3	133014	66278	1	0.000	A	A	A	A
MP3	133042	66292	1	0.000	A	A	A	A
MP3	133166	66326	1	0.000	A	A	A	A
MP3	133176	66331	1	0.000	A	A	A	A
MP3	133198	66342	1	0.000	A	A	A	A
MP3	133252	66369	1	0.000	A	A	A	A
MP3	133254	66370	1	0.000	A	A	A	A
MP3	133256	66371	1	0.000	A	A	A	A
MP3	133258	66372	1	0.000	A	A	A	A
MP3	133296	66391	1	0.000	A	A	A	A
MP3	133342	66414	1	0.000	A	A	A	A
WAV	130937	65355	2	0.300	A	A	A	A
WAV	131187	65460	2	0.300	A	A	A	A
WAV	131189	65461	2	0.300	A	A	A	A
WAV	131309	65521	2	0.300	A	A	A	A
WAV	131627	65596	2	0.300	A	A	A	A
WAV	131969	65767	2	0.300	A	A	A	A
WAV	131973	65769	2	0.300	A	A	A	A
WAV	131975	65770	2	0.300	A	A	A	A
WAV	131981	65773	2	0.300	A	A	A	A
WAV	131983	65774	2	0.300	A	A	A	A
WAV	132525	66033	2	0.300	A	A	A	A
WAV	132527	66034	2	0.300	A	A	A	A
WAV	132533	66037	2	0.300	A	A	A	A
WAV	132553	66047	2	0.300	A	A	A	A
WAV	132555	66048	2	0.300	A	A	A	A
WAV	132557	66049	2	0.300	A	A	A	A
WAV	132559	66050	2	0.300	A	A	A	A
WAV	132659	66100	2	0.300	A	A	A	A
WAV	132661	66101	2	0.300	A	A	A	A
WAV	132735	66138	2	0.300	A	A	A	A
WAV	132789	66165	2	0.300	A	A	A	A
WAV	132799	66170	2	0.300	A	A	A	A
WAV	132879	66210	2	0.300	A	A	A	A
WAV	132911	66226	2	0.300	A	A	A	A
WAV	132913	66227	2	0.300	A	A	A	A
WAV	132915	66228	2	0.300	A	A	A	A
WAV	132919	66230	2	0.300	A	A	A	A
WAV	133009	66275	2	0.300	A	A	A	A
WAV	133011	66276	2	0.300	A	A	A	A
WAV	133015	66278	2	0.300	A	A	A	A
WAV	133043	66292	2	0.300	A	A	A	A
WAV	133167	66326	2	0.300	A	A	A	A
WAV	133177	66331	2	0.300	A	A	A	A
WAV	133199	66342	2	0.300	A	A	A	A
WAV	133253	66369	2	0.300	A	A	A	A
WAV	133255	66370	2	0.300	A	A	A	A
WAV	133257	66371	2	0.300	A	A	A	A
WAV	133259	66372	2	0.300	A	A	A	A
WAV	133297	66391	2	0.300	A	A	A	A
WAV	133343	66414	2	0.300	A	A	A	A