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 = 564 
  AND c.category_id != 921 
ORDER BY 
  product_count DESC 
LIMIT 
  16;

Query time 0.01534

Result

category_id category product_count
572 Фронтальные погрузчики 680
574 Гусеничные экскаваторы 530
576 Автокраны 361
605 Экскаваторы-погрузчики 291
742 Мини-погрузчики 260
627 Мини-экскаваторы 213
708 Телескопические погрузчики 193
613 Бульдозеры колесные 188
607 Автобетоносмесители 142
815 Бетонное оборудование 132
565 Гусеничные бульдозеры 103
663 Колесные экскаваторы 97
817 Грохоты 82
810 Мини-тракторы 46
612 Автогидроподъёмники 33
610 Трубоукладчики 31