Originally, we just had each artefact store its parent ID. This is slow because it requires running multiple queries to find all the descendants of a node.
Then, we added a "path" element to each artefact. This is better, but you can't get a performance improvement by indexing the column, because most of the queries rely on the "LIKE" operator. (See https://bugs.launchpad.net/mahara/+bug/1423700 )
So if we want to squeeze more performance out of this, I think the one remaining thing to look into is the "nested set" technique. This technique results in very fast searches for descendants, with the cost of somewhat slower writes. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
There are even existing PHP libraries for using the technique, such as this one: http://www.sideralis.org/baobab/
Another idea would be to sniff the DB, and if they're using Postgres 8.4 or later, we use a recursive query on the artefact.parent column. Which, apparently, will probably be more performant than examining the "path" column or using the nested set technique. http:// www.postgresql. org/docs/ 8.4/static/ queries- with.html