mahara-contributors team mailing list archive

[Robert Lyon <robertl@xxxxxxxxxxxxxxx>]

** Changed in: mahara
    Milestone: 16.10.1 => 17.04.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/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