*** This bug is a duplicate of bug 1724603 ***
https://bugs.launchpad.net/bugs/1724603
This was fixed by https://bugs.launchpad.net/mahara/+bug/1724603
** Changed in: mahara
Milestone: 18.04.0 => None
** This bug has been marked a duplicate of bug 1724603
update_hierarchy_path in artefacts/lib.php hammers sql when copying
collections
--
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/1563582
Title:
Improve performance of queries against "artefact.path" by also
querying against artefact owner
Status in Mahara:
Confirmed
Bug description:
A while back we added a "path" column to the "artefact" table to try
to improve performance when looking up artefact descendants. This
replaced an older system where we only had a "parent" column, and when
we needed to query against artefact hierarchies we had to use repeated
SQL queries.
Unfortunately, though, the "path" column is a varchar, and it turns
out that varchar queries are not very performant even when indexed,
particularly when you're searching substrings of them as we do with
such queries. This causes big performance problems on sites with many
artefacts.
One easy way to drastically improve this performance, is that whenever
we have a query that primarily searches against "path", we also search
against the arteafct.owner, artefact.institution, or artefact.group
column as well. These columns are all indexed, and all integers, which
means they will perform very quickly. Also, with Mahara's current
architecture, there are no current situations where an artefact will
have a parent that is *not* owned by the same entity.
So, instead of doing "select * from artefact where path like '/' || ?
|| '/%'" to find all the children of a particular artefact, you can do
"select * from artefact where path like '/' || ? || '/%' AND owner=?"
(Though note that the actual query will need to check whether the
artefact is owned by an owner, group, or institution, and will need to
alter the query accordingly.)
To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1563582/+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
