Sandbox

SELECT 
  a.modifier, 
  a.modifier_type, 
  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, 
  a.variant_id 
FROM 
  cscart_product_option_variants a 
  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.variant_id IN (49, 54, 1)

Query time 0.00039

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	shared_option_variants	system	PRIMARY,company_id				0	const row not found
1	SIMPLE	a	range	PRIMARY	PRIMARY	3		3	Using index condition

Result

modifier	modifier_type	variant_id
0.000	A	1
0.000	A	49
0.000	A	54