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 (
    9540, 9539, 9538, 9537, 9536, 9535, 9534, 
    9532, 9531, 9460, 9492, 9488, 9481, 
    9480, 9479, 9478, 9477, 9476, 9475, 
    9474, 9473, 9472, 9471, 9470, 9469, 
    9468, 9467, 9466, 9465, 9464, 9463, 
    9462, 9461, 9459, 9458, 9457, 9456, 
    9455, 9454, 9453
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00136

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	18905	9453	1	0.000	A	A	A	A
MP3	18907	9454	1	0.000	A	A	A	A
MP3	18909	9455	1	0.000	A	A	A	A
MP3	18911	9456	1	0.000	A	A	A	A
MP3	18913	9457	1	0.000	A	A	A	A
MP3	18915	9458	1	0.000	A	A	A	A
MP3	18917	9459	1	0.000	A	A	A	A
MP3	18919	9460	1	0.000	A	A	A	A
MP3	18921	9461	1	0.000	A	A	A	A
MP3	18923	9462	1	0.000	A	A	A	A
MP3	18925	9463	1	0.000	A	A	A	A
MP3	18927	9464	1	0.000	A	A	A	A
MP3	18929	9465	1	0.000	A	A	A	A
MP3	18931	9466	1	0.000	A	A	A	A
MP3	18933	9467	1	0.000	A	A	A	A
MP3	18935	9468	1	0.000	A	A	A	A
MP3	18937	9469	1	0.000	A	A	A	A
MP3	18939	9470	1	0.000	A	A	A	A
MP3	18941	9471	1	0.000	A	A	A	A
MP3	18943	9472	1	0.000	A	A	A	A
MP3	18945	9473	1	0.000	A	A	A	A
MP3	18947	9474	1	0.000	A	A	A	A
MP3	18949	9475	1	0.000	A	A	A	A
MP3	18951	9476	1	0.000	A	A	A	A
MP3	18953	9477	1	0.000	A	A	A	A
MP3	18955	9478	1	0.000	A	A	A	A
MP3	18957	9479	1	0.000	A	A	A	A
MP3	18959	9480	1	0.000	A	A	A	A
MP3	18961	9481	1	0.000	A	A	A	A
MP3	18975	9488	1	0.000	A	A	A	A
MP3	18983	9492	1	0.000	A	A	A	A
MP3	19061	9531	1	0.000	A	A	A	A
MP3	19063	9532	1	0.000	A	A	A	A
MP3	19067	9534	1	0.000	A	A	A	A
MP3	19069	9535	1	0.000	A	A	A	A
MP3	19071	9536	1	0.000	A	A	A	A
MP3	19073	9537	1	0.000	A	A	A	A
MP3	19075	9538	1	0.000	A	A	A	A
MP3	19077	9539	1	0.000	A	A	A	A
MP3	19079	9540	1	0.000	A	A	A	A
WAV	18906	9453	2	0.300	A	A	A	A
WAV	18908	9454	2	0.300	A	A	A	A
WAV	18910	9455	2	0.300	A	A	A	A
WAV	18912	9456	2	0.300	A	A	A	A
WAV	18914	9457	2	0.300	A	A	A	A
WAV	18916	9458	2	0.300	A	A	A	A
WAV	18918	9459	2	0.300	A	A	A	A
WAV	18920	9460	2	0.300	A	A	A	A
WAV	18922	9461	2	0.300	A	A	A	A
WAV	18924	9462	2	0.300	A	A	A	A
WAV	18926	9463	2	0.300	A	A	A	A
WAV	18928	9464	2	0.300	A	A	A	A
WAV	18930	9465	2	0.300	A	A	A	A
WAV	18932	9466	2	0.300	A	A	A	A
WAV	18934	9467	2	0.300	A	A	A	A
WAV	18936	9468	2	0.300	A	A	A	A
WAV	18938	9469	2	0.300	A	A	A	A
WAV	18940	9470	2	0.300	A	A	A	A
WAV	18942	9471	2	0.300	A	A	A	A
WAV	18944	9472	2	0.300	A	A	A	A
WAV	18946	9473	2	0.300	A	A	A	A
WAV	18948	9474	2	0.300	A	A	A	A
WAV	18950	9475	2	0.300	A	A	A	A
WAV	18952	9476	2	0.300	A	A	A	A
WAV	18954	9477	2	0.300	A	A	A	A
WAV	18956	9478	2	0.300	A	A	A	A
WAV	18958	9479	2	0.300	A	A	A	A
WAV	18960	9480	2	0.300	A	A	A	A
WAV	18962	9481	2	0.300	A	A	A	A
WAV	18976	9488	2	0.300	A	A	A	A
WAV	18984	9492	2	0.300	A	A	A	A
WAV	19062	9531	2	0.300	A	A	A	A
WAV	19064	9532	2	0.300	A	A	A	A
WAV	19068	9534	2	0.300	A	A	A	A
WAV	19070	9535	2	0.300	A	A	A	A
WAV	19072	9536	2	0.300	A	A	A	A
WAV	19074	9537	2	0.300	A	A	A	A
WAV	19076	9538	2	0.300	A	A	A	A
WAV	19078	9539	2	0.300	A	A	A	A
WAV	19080	9540	2	0.300	A	A	A	A