materialized path performance problem
i have a table like this ( it stores categories in tree structure )
CREATE TABLE `instr_kategorie` (
`id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(100) NOT NULL default '',
`description` varchar(150) NOT NULL default '',
`depth` smallint(5) unsigned NOT NULL default '0',
`path` varchar(255) NOT NULL default '',
`visible` tinyint(1) NOT NULL default '0',
`sort` tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `visible` (`visible`),
KEY `path` (`path`),
KEY `depth` (`depth`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=95 ;
random row from the table:
(83, 'djjhgj', 'opis', 10, '1_2_4_7_58_61_62_63_82_83', 1, 0),
and the problem is when i try to pull out all items form the database and count all indirect children of an item.
QUERY
SELECT k. * , COUNT( d.id ) AS children FROM instr_kategorie AS k LEFT JOIN instr_kategorie AS d ON d.path LIKE CONCAT( k.path, '_%' ) GROUP BY k.id ORDER BY k.path ASC
the query works but is VERY slow
it executes 0.0266s for 70 rows :/ how can i optimise the query ?