materialized path performance problem
Posted by: Marcin Lulek
Date: July 11, 2006 05:17AM

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 ?

Options: ReplyQuote


Subject
Written By
Posted
materialized path performance problem
July 11, 2006 05:17AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.