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 (
    52804, 53161, 52672, 52933, 52859, 52731, 
    52449, 51844, 53015, 53014, 53016, 
    53013, 53012, 53011, 53010, 53009, 
    53008, 53007, 53006, 53005, 53004, 
    53003, 53002, 53001, 53000, 52999, 
    52998, 52997, 52996, 52995, 52994, 
    52993, 52992, 52991, 52990, 52989, 
    52988, 52987, 52986, 52985
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00140

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	103754	51844	1	0.000	A	A	A	A
MP3	104964	52449	1	0.000	A	A	A	A
MP3	105410	52672	1	0.000	A	A	A	A
MP3	105528	52731	1	0.000	A	A	A	A
MP3	105674	52804	1	0.000	A	A	A	A
MP3	105784	52859	1	0.000	A	A	A	A
MP3	105932	52933	1	0.000	A	A	A	A
MP3	106036	52985	1	0.000	A	A	A	A
MP3	106038	52986	1	0.000	A	A	A	A
MP3	106040	52987	1	0.000	A	A	A	A
MP3	106042	52988	1	0.000	A	A	A	A
MP3	106044	52989	1	0.000	A	A	A	A
MP3	106046	52990	1	0.000	A	A	A	A
MP3	106048	52991	1	0.000	A	A	A	A
MP3	106050	52992	1	0.000	A	A	A	A
MP3	106052	52993	1	0.000	A	A	A	A
MP3	106054	52994	1	0.000	A	A	A	A
MP3	106056	52995	1	0.000	A	A	A	A
MP3	106058	52996	1	0.000	A	A	A	A
MP3	106060	52997	1	0.000	A	A	A	A
MP3	106062	52998	1	0.000	A	A	A	A
MP3	106064	52999	1	0.000	A	A	A	A
MP3	106066	53000	1	0.000	A	A	A	A
MP3	106068	53001	1	0.000	A	A	A	A
MP3	106070	53002	1	0.000	A	A	A	A
MP3	106072	53003	1	0.000	A	A	A	A
MP3	106074	53004	1	0.000	A	A	A	A
MP3	106076	53005	1	0.000	A	A	A	A
MP3	106078	53006	1	0.000	A	A	A	A
MP3	106080	53007	1	0.000	A	A	A	A
MP3	106082	53008	1	0.000	A	A	A	A
MP3	106084	53009	1	0.000	A	A	A	A
MP3	106086	53010	1	0.000	A	A	A	A
MP3	106088	53011	1	0.000	A	A	A	A
MP3	106090	53012	1	0.000	A	A	A	A
MP3	106092	53013	1	0.000	A	A	A	A
MP3	106094	53014	1	0.000	A	A	A	A
MP3	106096	53015	1	0.000	A	A	A	A
MP3	106098	53016	1	0.000	A	A	A	A
MP3	106388	53161	1	0.000	A	A	A	A
WAV	103755	51844	2	0.300	A	A	A	A
WAV	104965	52449	2	0.300	A	A	A	A
WAV	105411	52672	2	0.300	A	A	A	A
WAV	105529	52731	2	0.300	A	A	A	A
WAV	105675	52804	2	0.300	A	A	A	A
WAV	105785	52859	2	0.300	A	A	A	A
WAV	105933	52933	2	0.300	A	A	A	A
WAV	106037	52985	2	0.300	A	A	A	A
WAV	106039	52986	2	0.300	A	A	A	A
WAV	106041	52987	2	0.300	A	A	A	A
WAV	106043	52988	2	0.300	A	A	A	A
WAV	106045	52989	2	0.300	A	A	A	A
WAV	106047	52990	2	0.300	A	A	A	A
WAV	106049	52991	2	0.300	A	A	A	A
WAV	106051	52992	2	0.300	A	A	A	A
WAV	106053	52993	2	0.300	A	A	A	A
WAV	106055	52994	2	0.300	A	A	A	A
WAV	106057	52995	2	0.300	A	A	A	A
WAV	106059	52996	2	0.300	A	A	A	A
WAV	106061	52997	2	0.300	A	A	A	A
WAV	106063	52998	2	0.300	A	A	A	A
WAV	106065	52999	2	0.300	A	A	A	A
WAV	106067	53000	2	0.300	A	A	A	A
WAV	106069	53001	2	0.300	A	A	A	A
WAV	106071	53002	2	0.300	A	A	A	A
WAV	106073	53003	2	0.300	A	A	A	A
WAV	106075	53004	2	0.300	A	A	A	A
WAV	106077	53005	2	0.300	A	A	A	A
WAV	106079	53006	2	0.300	A	A	A	A
WAV	106081	53007	2	0.300	A	A	A	A
WAV	106083	53008	2	0.300	A	A	A	A
WAV	106085	53009	2	0.300	A	A	A	A
WAV	106087	53010	2	0.300	A	A	A	A
WAV	106089	53011	2	0.300	A	A	A	A
WAV	106091	53012	2	0.300	A	A	A	A
WAV	106093	53013	2	0.300	A	A	A	A
WAV	106095	53014	2	0.300	A	A	A	A
WAV	106097	53015	2	0.300	A	A	A	A
WAV	106099	53016	2	0.300	A	A	A	A
WAV	106389	53161	2	0.300	A	A	A	A