*** This bug is a duplicate of bug 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 

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!

  Improve performance of queries against "artefact.path" by also
  querying against artefact owner

Status in Mahara:

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

  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:

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

Reply via email to