Page 2 of 2 First 12
  • Jump to page:
    #16
  1. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    The link above does have a workaround for MySQL using session variables:

    http://explainextended.com/2009/09/2...ed-sets-mysql/

    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;".
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  2. #17
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Originally Posted by Jacques1
    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.
    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.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #18
  5. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    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

    • aeternus agrees : for me its no option :) Thanks all!
    Last edited by Jacques1; February 18th, 2013 at 05:56 PM.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    1
    Rep Power
    0
    this is a valid solution and probably the most obvious one.
Page 2 of 2 First 12
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo