SELECT `items.catid`, `categories.name`, COUNT(`items.catid`) AS cnt FROM `items` JOIN `categories` ON `items.catid` = `categories.id`

SELECT `items`.`catid`, `categories`.`name`, COUNT(`items`.`catid`) AS cnt
FROM `items` JOIN `itemcat` ON `items`.`catid` = `categories`.`id`
GROUP BY `items`.`catid`

MariaDB [(none)]> CREATE DATABASE test CHARACTER SET 'utf8';
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use test;
Database changed

MariaDB [test]> CREATE TABLE categories (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> CREATE TABLE items (id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(255) NOT NULL, catid INT(10) UNSIGNED NOT NULL, KEY(catid), PRIMARY KEY(id), CONSTRAINT FOREIGN KEY (catid) REFERENCES categories (id) ON DELETE RESTRICT) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO categories (name) VALUES ('телефоны'), ('планшеты');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> INSERT INTO items (name, catid) VALUES ('samsung', 1), ('lenovo', 1), ('iphone', 1), ('ipad', 2), ('ASUS', 2), ('ACER', 2), ('EXPLAY', 2);
Query OK, 7 rows affected (0.00 sec)
Records: 7 Duplicates: 0 Warnings: 0

MariaDB [test]> DELETE FROM categories; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`items`, CONSTRAINT `items_ibfk_1` FOREIGN KEY (`catid`) REFERENCES `categories` (`id`))

MariaDB [test]> SELECT c.*, COUNT(p.id) as products_count FROM categories c JOIN items p ON c.id = p.catid GROUP BY c.id\G;
*************************** 1. row ***************************
id: 1
name: телефоны
products_count: 3
*************************** 2. row ***************************
id: 2
name: планшеты
products_count: 4
2 rows in set (0.00 sec)

MariaDB [test]> CREATE TABLE categories_aggregate (id INT(10) UNSIGNED NOT NULL, products_count INT(10) UNSIGNED NOT NULL, KEY(products_count), PRIMARY KEY(id), CONSTRAINT FOREIGN KEY (id) REFERENCES categories (id) ON DELETE CASCADE) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> LOCK TABLE categories WRITE, categories_aggregate WRITE, items WRITE;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO categories_aggregate SELECT categories.id, COUNT(items.id) FROM categories JOIN items ON categories.id = items.catid GROUP BY categories.id;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0

MariaDB [test]> SELECT * FROM categories_aggregate\G;
*************************** 1. row ***************************
id: 1
products_count: 3
*************************** 2. row ***************************
id: 2
products_count: 4
2 rows in set (0.00 sec)

MariaDB [test]> CREATE TRIGGER insert_items AFTER INSERT ON items FOR EACH ROW UPDATE categories_aggregate SET products_count = products_count+1 WHERE id = NEW.catid;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> CREATE TRIGGER delete_items AFTER DELETE ON items FOR EACH ROW UPDATE categories_aggregate SET products_count = products_count-1 WHERE id = OLD.catid;
Query OK, 0 rows affected (0.01 sec)

MariaDB [test]> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]> INSERT INTO items (name, catid) VALUES ('HUAWEI', 2);
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> SELECT * FROM categories_aggregate\G;
*************************** 1. row ***************************
id: 1
products_count: 3
*************************** 2. row ***************************
id: 2
products_count: 5
2 rows in set (0.00 sec)

MariaDB [test]> DELETE FROM items WHERE name = 'ipad';
Query OK, 1 row affected (0.00 sec)

MariaDB [test]> SELECT * FROM categories_aggregate\G;
*************************** 1. row ***************************
id: 1
products_count: 3
*************************** 2. row ***************************
id: 2
products_count: 4
2 rows in set (0.00 sec)