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 (
    75709, 75708, 75707, 75706, 75705, 75704, 
    75703, 75702, 75691, 75689, 75685, 
    75683, 75677, 75675, 75674, 75672, 
    75910, 75909, 75911, 75439
  ) 
  AND a.status = 'A' 
ORDER BY 
  a.position, 
  a.variant_id

Query time 0.00114

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		40	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	151414	75439	1	0.000	A	A	A	A
MP3	151880	75672	1	0.000	A	A	A	A
MP3	151884	75674	1	0.000	A	A	A	A
MP3	151886	75675	1	0.000	A	A	A	A
MP3	151890	75677	1	0.000	A	A	A	A
MP3	151902	75683	1	0.000	A	A	A	A
MP3	151906	75685	1	0.000	A	A	A	A
MP3	151914	75689	1	0.000	A	A	A	A
MP3	151918	75691	1	0.000	A	A	A	A
MP3	151940	75702	1	0.000	A	A	A	A
MP3	151942	75703	1	0.000	A	A	A	A
MP3	151944	75704	1	0.000	A	A	A	A
MP3	151946	75705	1	0.000	A	A	A	A
MP3	151948	75706	1	0.000	A	A	A	A
MP3	151950	75707	1	0.000	A	A	A	A
MP3	151952	75708	1	0.000	A	A	A	A
MP3	151954	75709	1	0.000	A	A	A	A
MP3	152354	75909	1	0.000	A	A	A	A
MP3	152356	75910	1	0.000	A	A	A	A
MP3	152358	75911	1	0.000	A	A	A	A
WAV	151415	75439	2	0.300	A	A	A	A
WAV	151881	75672	2	0.300	A	A	A	A
WAV	151885	75674	2	0.300	A	A	A	A
WAV	151887	75675	2	0.300	A	A	A	A
WAV	151891	75677	2	0.300	A	A	A	A
WAV	151903	75683	2	0.300	A	A	A	A
WAV	151907	75685	2	0.300	A	A	A	A
WAV	151915	75689	2	0.300	A	A	A	A
WAV	151919	75691	2	0.300	A	A	A	A
WAV	151941	75702	2	0.300	A	A	A	A
WAV	151943	75703	2	0.300	A	A	A	A
WAV	151945	75704	2	0.300	A	A	A	A
WAV	151947	75705	2	0.300	A	A	A	A
WAV	151949	75706	2	0.300	A	A	A	A
WAV	151951	75707	2	0.300	A	A	A	A
WAV	151953	75708	2	0.300	A	A	A	A
WAV	151955	75709	2	0.300	A	A	A	A
WAV	152355	75909	2	0.300	A	A	A	A
WAV	152357	75910	2	0.300	A	A	A	A
WAV	152359	75911	2	0.300	A	A	A	A