#1
  1. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115

    Possible optimizing a query


    Hi guys,

    I am not a real sql guru but with a bit of help of theinterwebz i created the following query

    Code:
    Select * from  tree_traversal where 
    lft > (SELECT lft FROM `tree_traversal` WHERE title = 'fruit') 
    and
     rgt < (SELECT rgt FROM `tree_traversal` WHERE title = 'fruit')
    as you see it uses 2 subqueries. I was wondering if it was possible with 1 subquery or can be optimised in any other way

    the dataset looks like this:
    Code:
    parent      title	lft	rgt
    ---------------------------------
                food	1	18
    food        fruit	2	11
    fruit       red	3	6
    red         cherry	4	5
    fruit       yellow	7	10
    yellow      banana	8	9
    food        meat	12	17
    meat        beef	13	14
    meat        pork	15	16
    @ 2:24 am.. sleep is overrated
    Last edited by aeternus; February 17th, 2013 at 08:28 PM.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    this looks fine to me. Alternatively, you could also use a join:
    Code:
    SELECT
    	children.title
    FROM
    	tree_traversal AS parents
    	JOIN tree_traversal AS children ON children.lft > parents.lft AND children.rgt < parents.rgt
    WHERE
    	parents.title = 'fruit'
    ORDER BY
    	children.lft
    ;
    Note that you don't need the "parent" attribute. But I guess you use it in case you wanna go back to the adjacency list model?

    By the way, which database system do you use?
    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".
  4. #3
  5. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Hi Jacques1,

    I think i'll give the modified pre ordered tree traversal method a chance. I use mysql (innodb), so it seems it might still be worth it due to the lack of a recursive query that postgresql and oracle have. It's also fun to try out Not sure if that means it's a wiser choice

    Your right about the parent field. I don't need that, I pretty much made a copy of the data in the article you gave. I left it in (for now) so I got some faster visual feedback when I am playing around with it.

    Due to the nature of these task categories (just one big list of categories and sub categories) I'm wondering though if I have worry a lot about selecting children from some category (I probably should ).

    As far as I see it now I will just show a big list of categories (with some indentation) from which one can choose. But I rather do it good from the start than making something awesome and find out later it needs to be overhauled.

IMN logo majestic logo threadwatch logo seochat tools logo