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 (
    56525, 56524, 56523, 56522, 56521, 56520, 
    56519, 56518, 56517, 56516, 56515, 
    56514, 56503, 56501, 56500, 56464, 
    56356, 55811, 54274, 56272, 56245, 
    56014, 56013, 55697, 55696, 55632, 
    54761, 54273, 56126, 55352, 55389, 
    55972, 55810, 55695, 54764, 54272, 
    56021, 55455, 55938, 55881
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00161

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	108610	54272	1	0.000	A	A	A	A
MP3	108612	54273	1	0.000	A	A	A	A
MP3	108614	54274	1	0.000	A	A	A	A
MP3	109588	54761	1	0.000	A	A	A	A
MP3	109594	54764	1	0.000	A	A	A	A
MP3	110770	55352	1	0.000	A	A	A	A
MP3	110844	55389	1	0.000	A	A	A	A
MP3	110976	55455	1	0.000	A	A	A	A
MP3	111330	55632	1	0.000	A	A	A	A
MP3	111456	55695	1	0.000	A	A	A	A
MP3	111458	55696	1	0.000	A	A	A	A
MP3	111460	55697	1	0.000	A	A	A	A
MP3	111686	55810	1	0.000	A	A	A	A
MP3	111688	55811	1	0.000	A	A	A	A
MP3	111828	55881	1	0.000	A	A	A	A
MP3	111942	55938	1	0.000	A	A	A	A
MP3	112010	55972	1	0.000	A	A	A	A
MP3	112092	56013	1	0.000	A	A	A	A
MP3	112094	56014	1	0.000	A	A	A	A
MP3	112108	56021	1	0.000	A	A	A	A
MP3	112318	56126	1	0.000	A	A	A	A
MP3	112556	56245	1	0.000	A	A	A	A
MP3	112610	56272	1	0.000	A	A	A	A
MP3	112780	56356	1	0.000	A	A	A	A
MP3	112996	56464	1	0.000	A	A	A	A
MP3	113068	56500	1	0.000	A	A	A	A
MP3	113070	56501	1	0.000	A	A	A	A
MP3	113074	56503	1	0.000	A	A	A	A
MP3	113096	56514	1	0.000	A	A	A	A
MP3	113098	56515	1	0.000	A	A	A	A
MP3	113100	56516	1	0.000	A	A	A	A
MP3	113102	56517	1	0.000	A	A	A	A
MP3	113104	56518	1	0.000	A	A	A	A
MP3	113106	56519	1	0.000	A	A	A	A
MP3	113108	56520	1	0.000	A	A	A	A
MP3	113110	56521	1	0.000	A	A	A	A
MP3	113112	56522	1	0.000	A	A	A	A
MP3	113114	56523	1	0.000	A	A	A	A
MP3	113116	56524	1	0.000	A	A	A	A
MP3	113118	56525	1	0.000	A	A	A	A
WAV	108611	54272	2	0.300	A	A	A	A
WAV	108613	54273	2	0.300	A	A	A	A
WAV	108615	54274	2	0.300	A	A	A	A
WAV	109589	54761	2	0.300	A	A	A	A
WAV	109595	54764	2	0.300	A	A	A	A
WAV	110771	55352	2	0.300	A	A	A	A
WAV	110845	55389	2	0.300	A	A	A	A
WAV	110977	55455	2	0.300	A	A	A	A
WAV	111331	55632	2	0.300	A	A	A	A
WAV	111457	55695	2	0.300	A	A	A	A
WAV	111459	55696	2	0.300	A	A	A	A
WAV	111461	55697	2	0.300	A	A	A	A
WAV	111687	55810	2	0.300	A	A	A	A
WAV	111689	55811	2	0.300	A	A	A	A
WAV	111829	55881	2	0.300	A	A	A	A
WAV	111943	55938	2	0.300	A	A	A	A
WAV	112011	55972	2	0.300	A	A	A	A
WAV	112093	56013	2	0.300	A	A	A	A
WAV	112095	56014	2	0.300	A	A	A	A
WAV	112109	56021	2	0.300	A	A	A	A
WAV	112319	56126	2	0.300	A	A	A	A
WAV	112557	56245	2	0.300	A	A	A	A
WAV	112611	56272	2	0.300	A	A	A	A
WAV	112781	56356	2	0.300	A	A	A	A
WAV	112997	56464	2	0.300	A	A	A	A
WAV	113069	56500	2	0.300	A	A	A	A
WAV	113071	56501	2	0.300	A	A	A	A
WAV	113075	56503	2	0.300	A	A	A	A
WAV	113097	56514	2	0.300	A	A	A	A
WAV	113099	56515	2	0.300	A	A	A	A
WAV	113101	56516	2	0.300	A	A	A	A
WAV	113103	56517	2	0.300	A	A	A	A
WAV	113105	56518	2	0.300	A	A	A	A
WAV	113107	56519	2	0.300	A	A	A	A
WAV	113109	56520	2	0.300	A	A	A	A
WAV	113111	56521	2	0.300	A	A	A	A
WAV	113113	56522	2	0.300	A	A	A	A
WAV	113115	56523	2	0.300	A	A	A	A
WAV	113117	56524	2	0.300	A	A	A	A
WAV	113119	56525	2	0.300	A	A	A	A