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 (
    47242, 47241, 47240, 47239, 47238, 47237, 
    47236, 47235, 47234, 47233, 47232, 
    47231, 47230, 47229, 47228, 47227, 
    47226, 47225, 47224, 47341, 47275, 
    47257, 47256, 47255, 47197, 47189, 
    47188, 47497, 47496, 47177, 47176, 
    47175, 47174, 47391, 47390, 47156, 
    47155, 47154, 47446, 47144, 47258, 
    47247, 47127, 47119, 47118, 47117, 
    47116, 47148, 47128, 47056, 47080, 
    47058, 47057, 47053, 47048, 47042, 
    47041, 47040, 47305, 47304, 47303, 
    47302, 47007, 47006, 47016, 47015, 
    47014, 47013, 46961, 46960, 46959, 
    46958, 46957, 53378, 47004, 47003, 
    46950, 46915, 46898, 47078
  ) 
  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		160	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	93862	46898	1	0.000	A	A	A	A
MP3	93896	46915	1	0.000	A	A	A	A
MP3	93966	46950	1	0.000	A	A	A	A
MP3	93980	46957	1	0.000	A	A	A	A
MP3	93982	46958	1	0.000	A	A	A	A
MP3	93984	46959	1	0.000	A	A	A	A
MP3	93986	46960	1	0.000	A	A	A	A
MP3	93988	46961	1	0.000	A	A	A	A
MP3	94072	47003	1	0.000	A	A	A	A
MP3	94074	47004	1	0.000	A	A	A	A
MP3	94078	47006	1	0.000	A	A	A	A
MP3	94080	47007	1	0.000	A	A	A	A
MP3	94092	47013	1	0.000	A	A	A	A
MP3	94094	47014	1	0.000	A	A	A	A
MP3	94096	47015	1	0.000	A	A	A	A
MP3	94098	47016	1	0.000	A	A	A	A
MP3	94146	47040	1	0.000	A	A	A	A
MP3	94148	47041	1	0.000	A	A	A	A
MP3	94150	47042	1	0.000	A	A	A	A
MP3	94162	47048	1	0.000	A	A	A	A
MP3	94172	47053	1	0.000	A	A	A	A
MP3	94178	47056	1	0.000	A	A	A	A
MP3	94180	47057	1	0.000	A	A	A	A
MP3	94182	47058	1	0.000	A	A	A	A
MP3	94222	47078	1	0.000	A	A	A	A
MP3	94226	47080	1	0.000	A	A	A	A
MP3	94298	47116	1	0.000	A	A	A	A
MP3	94300	47117	1	0.000	A	A	A	A
MP3	94302	47118	1	0.000	A	A	A	A
MP3	94304	47119	1	0.000	A	A	A	A
MP3	94320	47127	1	0.000	A	A	A	A
MP3	94322	47128	1	0.000	A	A	A	A
MP3	94354	47144	1	0.000	A	A	A	A
MP3	94362	47148	1	0.000	A	A	A	A
MP3	94374	47154	1	0.000	A	A	A	A
MP3	94376	47155	1	0.000	A	A	A	A
MP3	94378	47156	1	0.000	A	A	A	A
MP3	94414	47174	1	0.000	A	A	A	A
MP3	94416	47175	1	0.000	A	A	A	A
MP3	94418	47176	1	0.000	A	A	A	A
MP3	94420	47177	1	0.000	A	A	A	A
MP3	94442	47188	1	0.000	A	A	A	A
MP3	94444	47189	1	0.000	A	A	A	A
MP3	94460	47197	1	0.000	A	A	A	A
MP3	94514	47224	1	0.000	A	A	A	A
MP3	94516	47225	1	0.000	A	A	A	A
MP3	94518	47226	1	0.000	A	A	A	A
MP3	94520	47227	1	0.000	A	A	A	A
MP3	94522	47228	1	0.000	A	A	A	A
MP3	94524	47229	1	0.000	A	A	A	A
MP3	94526	47230	1	0.000	A	A	A	A
MP3	94528	47231	1	0.000	A	A	A	A
MP3	94530	47232	1	0.000	A	A	A	A
MP3	94532	47233	1	0.000	A	A	A	A
MP3	94534	47234	1	0.000	A	A	A	A
MP3	94536	47235	1	0.000	A	A	A	A
MP3	94538	47236	1	0.000	A	A	A	A
MP3	94540	47237	1	0.000	A	A	A	A
MP3	94542	47238	1	0.000	A	A	A	A
MP3	94544	47239	1	0.000	A	A	A	A
MP3	94546	47240	1	0.000	A	A	A	A
MP3	94548	47241	1	0.000	A	A	A	A
MP3	94550	47242	1	0.000	A	A	A	A
MP3	94560	47247	1	0.000	A	A	A	A
MP3	94576	47255	1	0.000	A	A	A	A
MP3	94578	47256	1	0.000	A	A	A	A
MP3	94580	47257	1	0.000	A	A	A	A
MP3	94582	47258	1	0.000	A	A	A	A
MP3	94616	47275	1	0.000	A	A	A	A
MP3	94670	47302	1	0.000	A	A	A	A
MP3	94672	47303	1	0.000	A	A	A	A
MP3	94674	47304	1	0.000	A	A	A	A
MP3	94676	47305	1	0.000	A	A	A	A
MP3	94748	47341	1	0.000	A	A	A	A
MP3	94846	47390	1	0.000	A	A	A	A
MP3	94848	47391	1	0.000	A	A	A	A
MP3	94958	47446	1	0.000	A	A	A	A
MP3	95058	47496	1	0.000	A	A	A	A
MP3	95060	47497	1	0.000	A	A	A	A
MP3	106822	53378	1	0.000	A	A	A	A
WAV	93863	46898	2	0.300	A	A	A	A
WAV	93897	46915	2	0.300	A	A	A	A
WAV	93967	46950	2	0.300	A	A	A	A
WAV	93981	46957	2	0.300	A	A	A	A
WAV	93983	46958	2	0.300	A	A	A	A
WAV	93985	46959	2	0.300	A	A	A	A
WAV	93987	46960	2	0.300	A	A	A	A
WAV	93989	46961	2	0.300	A	A	A	A
WAV	94073	47003	2	0.300	A	A	A	A
WAV	94075	47004	2	0.300	A	A	A	A
WAV	94079	47006	2	0.300	A	A	A	A
WAV	94081	47007	2	0.300	A	A	A	A
WAV	94093	47013	2	0.300	A	A	A	A
WAV	94095	47014	2	0.300	A	A	A	A
WAV	94097	47015	2	0.300	A	A	A	A
WAV	94099	47016	2	0.300	A	A	A	A
WAV	94147	47040	2	0.300	A	A	A	A
WAV	94149	47041	2	0.300	A	A	A	A
WAV	94151	47042	2	0.300	A	A	A	A
WAV	94163	47048	2	0.300	A	A	A	A
WAV	94173	47053	2	0.300	A	A	A	A
WAV	94179	47056	2	0.300	A	A	A	A
WAV	94181	47057	2	0.300	A	A	A	A
WAV	94183	47058	2	0.300	A	A	A	A
WAV	94223	47078	2	0.300	A	A	A	A
WAV	94227	47080	2	0.300	A	A	A	A
WAV	94299	47116	2	0.300	A	A	A	A
WAV	94301	47117	2	0.300	A	A	A	A
WAV	94303	47118	2	0.300	A	A	A	A
WAV	94305	47119	2	0.300	A	A	A	A
WAV	94321	47127	2	0.300	A	A	A	A
WAV	94323	47128	2	0.300	A	A	A	A
WAV	94355	47144	2	0.300	A	A	A	A
WAV	94363	47148	2	0.300	A	A	A	A
WAV	94375	47154	2	0.300	A	A	A	A
WAV	94377	47155	2	0.300	A	A	A	A
WAV	94379	47156	2	0.300	A	A	A	A
WAV	94415	47174	2	0.300	A	A	A	A
WAV	94417	47175	2	0.300	A	A	A	A
WAV	94419	47176	2	0.300	A	A	A	A
WAV	94421	47177	2	0.300	A	A	A	A
WAV	94443	47188	2	0.300	A	A	A	A
WAV	94445	47189	2	0.300	A	A	A	A
WAV	94461	47197	2	0.300	A	A	A	A
WAV	94515	47224	2	0.300	A	A	A	A
WAV	94517	47225	2	0.300	A	A	A	A
WAV	94519	47226	2	0.300	A	A	A	A
WAV	94521	47227	2	0.300	A	A	A	A
WAV	94523	47228	2	0.300	A	A	A	A
WAV	94525	47229	2	0.300	A	A	A	A
WAV	94527	47230	2	0.300	A	A	A	A
WAV	94529	47231	2	0.300	A	A	A	A
WAV	94531	47232	2	0.300	A	A	A	A
WAV	94533	47233	2	0.300	A	A	A	A
WAV	94535	47234	2	0.300	A	A	A	A
WAV	94537	47235	2	0.300	A	A	A	A
WAV	94539	47236	2	0.300	A	A	A	A
WAV	94541	47237	2	0.300	A	A	A	A
WAV	94543	47238	2	0.300	A	A	A	A
WAV	94545	47239	2	0.300	A	A	A	A
WAV	94547	47240	2	0.300	A	A	A	A
WAV	94549	47241	2	0.300	A	A	A	A
WAV	94551	47242	2	0.300	A	A	A	A
WAV	94561	47247	2	0.300	A	A	A	A
WAV	94577	47255	2	0.300	A	A	A	A
WAV	94579	47256	2	0.300	A	A	A	A
WAV	94581	47257	2	0.300	A	A	A	A
WAV	94583	47258	2	0.300	A	A	A	A
WAV	94617	47275	2	0.300	A	A	A	A
WAV	94671	47302	2	0.300	A	A	A	A
WAV	94673	47303	2	0.300	A	A	A	A
WAV	94675	47304	2	0.300	A	A	A	A
WAV	94677	47305	2	0.300	A	A	A	A
WAV	94749	47341	2	0.300	A	A	A	A
WAV	94847	47390	2	0.300	A	A	A	A
WAV	94849	47391	2	0.300	A	A	A	A
WAV	94959	47446	2	0.300	A	A	A	A
WAV	95059	47496	2	0.300	A	A	A	A
WAV	95061	47497	2	0.300	A	A	A	A
WAV	106823	53378	2	0.300	A	A	A	A