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

Bug #1563582 reported by Aaron Wells
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Mahara
Confirmed
Medium
Unassigned

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

Aaron Wells (u-aaronw)
Changed in mahara:
status: New → Confirmed
importance: Undecided → Medium
milestone: none → 16.10.0
assignee: nobody → Aaron Wells (u-aaronw)
Robert Lyon (robertl-9)
Changed in mahara:
milestone: 16.10.0 → 16.10.1
Robert Lyon (robertl-9)
Changed in mahara:
milestone: 16.10.1 → 17.04.0
Changed in mahara:
assignee: Aaron Wells (u-aaronw) → nobody
milestone: 17.04.0 → 17.10.0
Robert Lyon (robertl-9)
Changed in mahara:
milestone: 17.10.0 → 18.04.0
Revision history for this message
Robert Lyon (robertl-9) wrote :
Changed in mahara:
milestone: 18.04.0 → none
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.