SELECT `parent`.`id_sklep_kategoria`,
       (COUNT(sklep_kategorie_produkty.id_sklep_produkt)) AS `ilosc`
FROM `sklep_kategorie` AS `node`
INNER JOIN `sklep_kategorie` AS `parent`
INNER JOIN `sklep_kategorie_produkty`
WHERE (node.lft BETWEEN parent.lft AND parent.rght)
  AND (node.id_sklep_kategoria = sklep_kategorie_produkty.id_sklep_kategoria)
  AND (node.visible = 1)
GROUP BY `parent`.`id_sklep_kategoria`

"id_sklep_kategoria"(category id),
"id_sklep_produkt"(product id)

+----+-------------+--------------------------+------+--------------------+--------------------+---------+-----------------------------------+------+------------------------------------------------+
| id | select_type | table                    | type | possible_keys      | key                | key_len | ref                               | rows | Extra                                          |
+----+-------------+--------------------------+------+--------------------+--------------------+---------+-----------------------------------+------+------------------------------------------------+
|  1 | SIMPLE      | node                     | ALL  | PRIMARY,lft        | NULL               | NULL    | NULL                              |  983 | Using where; Using temporary; Using filesort   |
|  1 | SIMPLE      | sklep_kategorie_produkty | ref  | id_sklep_kategoria | id_sklep_kategoria | 4       | c2_330_1.node.id_sklep_kategoria  |    9 | Using index                                    |
|  1 | SIMPLE      | parent                   | ALL  | lft,rght           | NULL               | NULL    | NULL                              |  983 | Range checked for each record (index map: 0x6) |
+----+-------------+--------------------------+------+--------------------+--------------------+---------+-----------------------------------+------+------------------------------------------------+
3 rows in set (0.00 sec)