Database Search Tree Query

Question

I have a table in my database representing a tree. Data is saved using nested sets . I want to write a query to search the tree and return only the nodes that match the pattern, along with their ancestors and descendants. This is what I came up with.

SELECT DISTINCT Node, Parent, Description
FROM Hierarchy 
INNER JOIN 
    (SELECT Lft, Rgt 
    FROM Hierarchy 
    WHERE Description LIKE '%SEARCHQUERY%') AS Matches 
ON (Hierarchy.Lft <= Matches.Lft AND 
    Hierarchy.Rgt >= Matches.Rgt) OR 
    (Hierarchy.Lft >= Matches.Lft AND 
    Hierarchy.Rgt <= Matches.Rgt) 
ORDER BY Description

This query works, but it's a little slow when the subquery matches a lot of descriptions. I am looking for ideas on how to improve the performance of this query.

If relevant, I use Access.

I am free and ready to change the structure of the table to improve this query. The table has about 8000 nodes. The number of entries will not change much during the life of the application. The maximum depth is five.

( , ~ 200 ), (, , ).

Answer 1

, , , :

, , , , , " ", .

Faroult " SQL" , node , "", node. ( . Slashdot).

-, - Art of SQL , , ( " ", " / ", " " ) ( " " ", Y" ).

Faroult , , , , () .

Answer 2

parent_id , hierarchy ( ), ( ) ( ).

Answer 3

, , - LIKE('%blah%'). %, . , Access FULLTEXT, MATCH(Description) AGAINST ('blah')