Peter Normann wrote : > > Hello, > > I have a query I hope somebody can help me optimize. I have two tables > (given example): > > CREATE TABLE area ( > Area_ID INTEGER UNSIGNED, > Master INTEGER UNSIGNED > ); > > CREATE TABLE manager ( > Manager_ID INTEGER UNSIGNED, > Person INTEGER UNSIGNED, > Area INTEGER UNSIGNED > ); > > I have an organization, that has three levels. The top level area has > Master IS NULL, the second and third level having Master reference their > parent area. Lets call area level 1 = area, level 2 = team, level 3 = > group. > > Suppose I have a manager for an area (level 1) and I would like to get > all areas that he manages a long with all subareas (teams and group) > that belongs to his areas. I go > > SELECT distinct area.Area_ID Area_ID > FROM manager manager, area area, area team, area group > WHERE manager.Person='1' AND > (manager.Area = area.Area_ID || > (manager.Area=team.Area_ID && team.Area_ID=area.Master) || > (manager.Area=group.Area_ID && group.Area_ID=team.Master && > team.Area_ID=area.Master)); > > This query is painfully slow. I guess it produces millions and millions > of table scans having only a couple of 157 area records. > > I have tried to make al sorts of indexes with Area_ID, Master as single > and multi-column indexes etc... When I do a > > mysql> explain SELECT distinct area.Area_ID Area_ID FROM manager > manager, area area, area team, area hold WHERE manager.Person='1' AND > manager.Area = area.Area_ID || (manager.Area=team.Area_ID && > team.Area_ID=area.Master) || (manager.Area=hold.Area_ID && > hold.Area_ID=team.Master && team.Area_ID=area.Master); > +---------+-------+--------------------+------------+---------+------+-- > ----+---------------------------------------------------------+ > | table | type | possible_keys | key | key_len | ref | > rows | Extra | > +---------+-------+--------------------+------------+---------+------+-- > ----+---------------------------------------------------------+ > | area | index | PRIMARY,area_index | areamaster | 11 | NULL | > 157 | Using index; Using temporary | > | hold | index | PRIMARY,area_index | PRIMARY | 4 | NULL | > 157 | Using index; Distinct | > | team | index | PRIMARY,area_index | areamaster | 11 | NULL | > 157 | Using index; Distinct | > | manager | ALL | manager_area | NULL | NULL | NULL | > 186 | range checked for each record (index map: 16); Distinct | > +---------+-------+--------------------+------------+---------+------+-- > ----+---------------------------------------------------------+ > > Any help will be appreciated, since I use this query in a number of > functions and it is a big problem... > > Best regards > > Peter Normann >
Hi, Since you didn't show us how you index these tables, just a silly question: have you an index on manager(person) ? Regards -- Joseph Bueno NetClub/Trader.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
