SELECT 
  cscart_products.tracking, 
  cscart_products.amount, 
  cscart_products.min_qty, 
  cscart_products.max_qty, 
  cscart_products.qty_step, 
  cscart_products.list_qty_count, 
  cscart_product_descriptions.product 
FROM 
  cscart_products 
  LEFT JOIN cscart_product_descriptions ON cscart_product_descriptions.product_id = cscart_products.product_id 
  AND lang_code = 'en' 
WHERE 
  cscart_products.product_id = 672

Query time 0.00025

Explain
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cscart_products const PRIMARY PRIMARY 3 const 1
1 SIMPLE cscart_product_descriptions const PRIMARY,product_id PRIMARY 9 const,const 1

Result

tracking amount min_qty max_qty qty_step list_qty_count product
B 0.00 42 0 42 0 Atlas Stopter K-20 - 2 in 1 | adhesive for EPS, XPS boards and for mesh embedding