Bug #32174 MySQL needs the hierarchy support
Submitted: 7 Nov 2007 22:19 Modified: 8 Dec 2017 16:24
Reporter: Yurij Zagrebnoy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any
Tags: hierarchical, hierarchy, queries, support, tree
Triage: Triaged: D5 (Feature request)

[7 Nov 2007 22:19] Yurij Zagrebnoy
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?)
[8 Nov 2007 11:17] Miguel Solorzano
Thank you for the bug report feature request.
[11 Feb 2014 20:15] Daniƫl van Eeden
This looks like a duplicate of Bug #16244
[30 Sep 2016 6:51] Guilhem Bichot
A labs release of CTEs is available here:
http://mysqlserverteam.com/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes
[8 Dec 2017 16:24] Guilhem Bichot
Posted by developer:
 
Recursive CTEs are in MySQL 8.0.1 and newer.