SELECT 
  c.category_id, 
  cd.category, 
  (
    SELECT 
      COUNT(DISTINCT p.product_id) 
    FROM 
      cscart_products p 
      JOIN cscart_products_categories pc ON pc.product_id = p.product_id 
      JOIN cscart_categories sub_c ON pc.category_id = sub_c.category_id 
    WHERE 
      (
        sub_c.id_path LIKE CONCAT(c.id_path, '/%') 
        OR sub_c.category_id = c.category_id
      ) 
      AND p.status = 'A'
  ) AS product_count 
FROM 
  cscart_categories c 
  JOIN cscart_category_descriptions cd ON c.category_id = cd.category_id 
WHERE 
  c.parent_id = 597 
  AND c.category_id != 906 
ORDER BY 
  product_count DESC 
LIMIT 
  16;

Query time 0.00753

Result

category_id category product_count
598 Полуприцепы бензовозы 120
738 Полуприцепы тяжеловозы 112
608 Самосвальные полуприцепы 111
602 Полуприцепы зерновозы 72
609 Полуприцепы контейнеровозы 61
689 Прицепы тралы 60
599 Полуприцепы битумовозы 52
687 Самосвальные прицепы 40
603 Тентованные полуприцепы 20
908 Бортовые полуприцепы 18
688 Прицепы зерновозы 9
631 Цементораспределители 2
600 Цементовозы 1
601 Нефтевозы 1
920 Полуприцепы-автовозы 1
922 Полуприцепы рефрижераторы 1