Comments on: Introduction to MySQL 8.0 Recursive Common Table Expression (Part 2)

By: Dean Perry

Dean Perry — Wed, 02 Jun 2021 12:11:17 +0000

In reply to Øystein Grøvlen. Thanks for your message Øystein. Your message got me thinking! (I'm not sure if this is exactly what you had in mind.) I added a concatenated field using all of the sort order values we had in the database, appending a 0 so we could sort correctly: CONCAT(cp.sortpath, ',', LPAD(p.SortOrder,2,'0')) as sortpath which we then sort by: select * from cat_path ORDER BY sortpath; I can now retrieve a hierarchically sorted list -Root Category --Category 1 ---Sub Category 1.1 ---Sub Category 1.2 --Category 2 and so on. I really appreciate your comment! (It would of course be nicer to not have to add this extra field and to have MySQL do it automatically, but this is awesome.) Many thanks, all the way from Australia

By: Øystein Grøvlen

Øystein Grøvlen — Wed, 02 Jun 2021 08:37:59 +0000

In reply to Dean Perry. Why do you need the ordering to be applied to the CTE? Can the ORDER BY be added to the main query?

By: Dean Perry

Dean Perry — Tue, 01 Jun 2021 22:49:58 +0000

In reply to Corrado Pandiani. Thanks Corrado. For recursive queries of a tree structure it's really difficult to keep items in order if you can't add an ORDER BY clause. I run a product information management (PIM) SaaS and we can't default to the primary key / insert order as new categories are being created and moved around all the time. Extracting our tree using an example similar to your CTE above reduces the time to traverse the entire tree of e.g. ~5,000 products to a fraction of a second, but in the wrong order. We end up having to break the query into its component parts via code which is much slower! Is Oracle open to sharing this type of information? Frankly, it's almost to the point where we are looking at changing databases away from MySQL as we need less code and better performance.

By: Corrado Pandiani

Corrado Pandiani — Tue, 01 Jun 2021 14:01:02 +0000

In reply to Dean Perry. Hi dean. I've no details about that, We should ask Oracle.

By: Dean Perry

Dean Perry — Mon, 31 May 2021 11:53:11 +0000

Any ideas on if/when MySQL might support ORDER BY within a recursive CTE?

By: Adnan

Adnan — Thu, 13 Feb 2020 23:15:08 +0000

Nice i will soon practice it

By: Øystein Grøvlen

Øystein Grøvlen — Thu, 13 Feb 2020 19:06:04 +0000

In MySQL 8.0.19 it is possible to use the LIMIT clause to limit the recursion: https://mysqlserverteam.com/a-new-simple-way-to-figure-out-why-your-recursive-cte-is-running-away/