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 = 8365 
  AND c.company_id = 2 
ORDER BY 
  is_main DESC

Query time 0.00033

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE pc ref PRIMARY,pt,product_id_idx,category_id_idx,product_category_idx,category_product_idx pt 3 const 3 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
499 0