February 17th, 2013, 08:24 PM
Possible optimizing a query
I am not a real sql guru but with a bit of help of theinterwebz i created the following query
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
Select * from tree_traversal where
lft > (SELECT lft FROM `tree_traversal` WHERE title = 'fruit')
rgt < (SELECT rgt FROM `tree_traversal` WHERE title = 'fruit')
the dataset looks like this:
@ 2:24 am.. sleep is overrated
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
Last edited by aeternus; February 17th, 2013 at 08:28 PM.
February 18th, 2013, 08:13 AM
this looks fine to me. Alternatively, you could also use a join:
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?
tree_traversal AS parents
JOIN tree_traversal AS children ON children.lft > parents.lft AND children.rgt < parents.rgt
parents.title = 'fruit'
By the way, which database system do you use?
February 18th, 2013, 10:03 AM
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.