Sandbox

SELECT 
  DISTINCT pc.category_id, 
  count(DISTINCT pc.product_id) as total_products 
FROM 
  cscart_products AS products 
  LEFT JOIN cscart_products_categories AS pc ON products.product_id = pc.product_id 
WHERE 
  0 
GROUP BY 
  pc.category_id

Query time 0.00017

JSON explain

{
  "query_block": {
    "select_id": 1,
    "table": {
      "message": "Impossible WHERE"
    }
  }
}