Description:
Today's applications often require hierarchy processing capabilities. Although there are ways to implement hierarchy processing using current MySQL versions, all of them have their drawbacks.
Hierarchy could be implemented by the client application issuing multiple queries to fetch the tree and do required actions on it. While this is appropriate for small application (small amount of data or low speed requirements) it's completely unacceptable for large ones.
Also hierarchy could be implemented with stored procedures. Although this reduces communication overhead between client and server, this approach is very slow due to interpreted nature of SPs. Besides optimizer can do nothing with them as to hierarchy processing.
Imagine a real-world example: there is a table with millions of records that have parent-child relations (each record has ID and ParentID fields). As a search result you have tens of thousands of records selected from this table and you need to check which of them are allowed to be displayed to the current user basing on theirs or their parents' security settings. How much time will it take to do that using any of available approaches? Too much. There are dozens of such examples.
Major competitors already have hierarchy processing support. MS SQL Server 2005 offers CTE (http://msdn2.microsoft.com/en-us/library/ms186243.aspx) which is a really ugly way of trees processing. Oracle, IMHO, offers the most comprehensive features for doing this (http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries003.htm). Why not to adopt them to MySQL? Using Oracle for application mentioned in example above resulted in more than order-of-magnitude performance increase, but I like MySQL and want to use it for my projects.
How to repeat:
see description (if I want to request a feature, why do I need to fill the "How to repeat" field?)
