If a different perspective may be helpful to you: If moving overhead to writes is an option (ie you dont have many or time critical writes), then the tree descendants problem can be sped up to stellar speeds by using a path column.
IE. add a column "path" in the nodes table that would contain something like "1.2.3" for node 3 that is descendant of node 2 that is descendant of node 1. Then querying 2's descendant would result in the following range: "path">='1.2.' AND "path"<'1.2/' or you can try using LIKE semantics - both can use an index if you are careful with collation; I found out the hard way that the range queries are more resilient and portable, SQLite and others have a pretty awkward way of plugging in the LIKE optimisations that may result in the index being skipped for not-so-obvious reasons. Inserting nodes is trivial, but moving edges requires an algorithm to update paths (whenever a node's parent changes, all descendant's paths must be updated). However, for most real-world ontology use scenarios, this opperation happens very rarely and usually on the admin range of functions, so you can afford this operation that can be pretty slow. -- Sent from: http://sqlite.1065341.n5.nabble.com/ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
