Sandbox

SELECT 
  c.product_details_view 
FROM 
  cscart_products_categories as pc 
  LEFT JOIN cscart_categories as c ON pc.category_id = c.category_id 
WHERE 
  pc.product_id = 672 
  AND pc.link_type = 'M'

Query time 0.00030

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	pc	ref	link_type,pt	pt	6	const,const	1	Using index condition
1	SIMPLE	c	eq_ref	PRIMARY,p_category_id,idx_category_id	PRIMARY	3	mahm3t_cs443.pc.category_id	1

Result

product_details_view
default