Sandbox

SELECT 
  p.product_code, 
  cd.category, 
  pd.product, 
  pp.price 
FROM 
  cscart_products AS p 
  LEFT JOIN cscart_products_categories AS pc ON (pc.product_id = p.product_id) 
  LEFT JOIN cscart_category_descriptions AS cd ON (
    cd.category_id = pc.category_id 
    AND cd.lang_code = 'en'
  ) 
  LEFT JOIN cscart_product_prices AS pp ON (pp.product_id = p.product_id) 
  LEFT JOIN cscart_product_descriptions AS pd ON (
    pd.product_id = p.product_id 
    AND pd.lang_code = 'en'
  ) 
WHERE 
  p.product_id = 101 
  AND pp.lower_limit = 1

Query time 0.00027

Explain
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	p	const	PRIMARY	PRIMARY	3	const	1
1	SIMPLE	pd	const	PRIMARY,product_id	PRIMARY	9	const,const	1
1	SIMPLE	pc	ref	pt	pt	3	const	1
1	SIMPLE	pp	ref	usergroup,product_id,lower_limit	usergroup	3	const	1	Using index condition
1	SIMPLE	cd	eq_ref	PRIMARY	PRIMARY	9	cscart_dev_4_16.pc.category_id,const	1	Using where

Result

product_code	category	product	price
E01010TOGW	Nokia	Nokia X3-02	188.88