February 18th, 2013, 05:21 PM
The link above does have a workaround for MySQL using session variables:
However, it's pretty slow: 600ms for retrieving all children of a given node, while the nested sets only take 15ms.
So nested sets are really the only general solution that runs on all DBS with decent speed and without any limitations regarding the tree depth.
I mean, yeah, we probably shouldn't be bashing MySQL all the time. But that DBS just has so many weaknesses and weirdnesses. It seems you run into trouble whenever you have to do something more complex than "SELECT name FROM users WHERE user_id = 1;".
February 18th, 2013, 05:37 PM
Recursive queries are pretty ubiquitous and a "standard" feature nowadays. MySQL is pretty much the only one not supporting that - at least when it comes to "server based" DBMS. Even some of the "small" embedded engines like H2 support this. Not having this is like not supporting outer joins.
Originally Posted by Jacques1
I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
Tips on how to ask better questions:
February 18th, 2013, 05:54 PM
Hey, you're preaching to the choir!
Trust me, I'm no fan of MySQL (which you might have guessed), and I'd love to stop fumbling with workarounds for that DBS. But we have to deal with the fact that MySQL is still pretty popular and the first choice for many people just starting to play with dynamic websites on some cheap webspace. And it's still widely used in legacy software (for exactly that reason, I guess).
So as much as I'd like to say: F*ck it, take PostgreSQL and write a recursive CTE -- it's not an option for many people.
Comments on this post
Last edited by Jacques1; February 18th, 2013 at 05:56 PM.
March 21st, 2013, 08:21 AM
this is a valid solution and probably the most obvious one.