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 --------------------------------------------------------------------- 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
