Sandbox

SELECT 
  p.tracking, 
  p.amount, 
  p.min_qty, 
  p.max_qty, 
  p.qty_step, 
  p.list_qty_count, 
  p.out_of_stock_actions, 
  p.product_type, 
  pd.product 
FROM 
  cscart_products AS p 
  LEFT JOIN cscart_product_descriptions AS pd ON pd.product_id = p.product_id 
  AND lang_code = 'en' 
WHERE 
  p.product_id = 2049

Query time 0.00041

JSON explain

{
  "query_block": {
    "select_id": 1,
    "message": "no matching row in const table"
  }
}