SELECT 
  c.category_id, 
  cd.category, 
  city.city, 
  city.link, 
  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_product_stocks ps ON ps.product_id = p.product_id 
  JOIN cscart_company_stocks cs ON cs.stock_id = ps.stock_id 
  JOIN cscart_custom_city city ON city.fias_id = cs.fias_id 
WHERE 
  c.id_path LIKE '%740%' 
  AND p.status = 'A' 
GROUP BY 
  city.city 
ORDER BY 
  product_count DESC 
LIMIT 
  16;

Query time 0.00240

Result

category_id category city link product_count
740 Легковые автомобили Мытищи mytishchi 16
740 Легковые автомобили Москва moscow 12
740 Легковые автомобили Щербинка 10
740 Легковые автомобили Челябинск chelyabinsk 8
740 Легковые автомобили Воронеж voronegh 4
740 Легковые автомобили Красноярск krasnoyarsk 2
740 Легковые автомобили Тюмень tyumen 2
740 Легковые автомобили Уфа ufa 2
740 Легковые автомобили Екатеринбург ekaterinburg 1
740 Легковые автомобили Иркутск irkutsk 1
740 Легковые автомобили Казань kazan 1
740 Легковые автомобили Калуга kaluga 1
740 Легковые автомобили Белгород belgorod 1
740 Легковые автомобили Ростов-на-Дону rostov-on-don 1
740 Легковые автомобили Самара samara 1
740 Легковые автомобили Тула tula 1