SELECT 
  c.category_id, 
  cd.category, 
  b.brand, 
  b.brand_id, 
  COUNT(DISTINCT p.product_id) AS product_count 
FROM 
  cscart_products p 
  JOIN cscart_products_categories pc ON pc.product_id = p.product_id 
  JOIN cscart_categories c ON pc.category_id = c.category_id 
  JOIN cscart_category_descriptions cd ON c.category_id = cd.category_id 
  JOIN cscart_brands b ON p.brand_id = b.brand_id 
WHERE 
  c.id_path LIKE '%740%' 
  AND p.status = 'A' 
GROUP BY 
  b.brand 
ORDER BY 
  product_count DESC 
LIMIT 
  16;

Query time 0.00135

Result

category_id category brand brand_id product_count
740 Легковые автомобили ГАЗ 2312 16
740 Легковые автомобили BMW 3107 6
740 Легковые автомобили Lada (ВАЗ) 3096 5
740 Легковые автомобили TOYOTA 1942 4
740 Легковые автомобили MERCEDES 1247 4
740 Легковые автомобили Lexus 3099 4
740 Легковые автомобили CHERY 318 4
740 Легковые автомобили HAVAL 3086 3
740 Легковые автомобили Geely 3087 2
740 Легковые автомобили JAC 911 2
740 Легковые автомобили EXEED 3089 2
740 Легковые автомобили Tank 3094 2
740 Легковые автомобили HYUNDAI 869 1
740 Легковые автомобили GAC 3114 1
740 Легковые автомобили FERRARI 594 1
740 Легковые автомобили Li 3113 1