mahara-contributors team mailing list archive

[Aaron Wells <1563582@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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.)

** Affects: mahara
     Importance: Medium
     Assignee: Aaron Wells (u-aaronw)
         Status: Confirmed

** Changed in: mahara
       Status: New => Confirmed

** Changed in: mahara
   Importance: Undecided => Medium

** Changed in: mahara
    Milestone: None => 16.10.0

** Changed in: mahara
     Assignee: (unassigned) => Aaron Wells (u-aaronw)

-- 
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