Public bug reported: 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/ ** Affects: mahara Importance: Wishlist Status: Triaged ** Tags: performance ** Changed in: mahara Importance: Undecided => Wishlist ** Changed in: mahara Milestone: None => 15.10.0 -- You received this bug notification because you are a member of Mahara Contributors, which is subscribed to Mahara. Matching subscriptions: Subscription for all Mahara Contributors -- please ask on #mahara-dev or mahara.org forum before editing or unsubscribing it! https://bugs.launchpad.net/bugs/1427885 Title: Change "artefact.path" column to use the "nested set" technique for managing hierarchical data Status in Mahara ePortfolio: Triaged Bug description: 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/ To manage notifications about this bug go to: https://bugs.launchpad.net/mahara/+bug/1427885/+subscriptions _______________________________________________ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp