On Sat, 2005-03-26 at 10:29 -0500, Jason Jobe wrote: > // to find people whose great-grandfather's name is Fred > Well, this is probably correct sql but hopefully you get the idea. So > part of the question is: > > 1. How efficient is this?
Not at all. It doesn't work. If you meant: select p1.* from person as p1, person as p2, person as p3 where p3.name='Fred' and p3.parent_rowid = p2.rowid and p2.parent_rowid = p1.rowid; then it's still not very good- as fast as three integer lookups and one string lookup. An index would help that string lookup. More so if it could be unique. > 2. Is there an ordering that would make it much more efficient? Ordering? > 3. Would it be better to write the custom function "related-to" to use > its own index? Not likely. > 4. Can a custom function leverage the sqlite data structures is some > low level ways to gain efficiency? Sort of. SQL doesn't have trees. It looks like you're willing to commit to the cost of maintaining transitive closure of graphs, but I don't believe you have to. See: http://www.intelligententerprise.com/001020/celko.jhtml
