SELECT 
  c.category_id, 
  IF(pc.link_type = 'M', 1, 0) as is_main 
FROM 
  cscart_categories AS c 
  LEFT JOIN cscart_products_categories AS pc ON c.category_id = pc.category_id 
WHERE 
  pc.product_id = 672 
  AND c.company_id = 1 
ORDER BY 
  is_main DESC

Query time 0.00034

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pc ref PRIMARY,pt pt 3 const 4 Using where; Using filesort
1 SIMPLE c eq_ref PRIMARY,p_category_id,idx_category_id PRIMARY 3 mahm3t_cs443.pc.category_id 1 Using where

Result

category_id is_main
450 1