Improve performance of queries against "artefact.path" by also querying against artefact owner
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.
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.)
Changed in mahara: | |
status: | New → Confirmed |
importance: | Undecided → Medium |
milestone: | none → 16.10.0 |
assignee: | nobody → Aaron Wells (u-aaronw) |
Changed in mahara: | |
milestone: | 16.10.0 → 16.10.1 |
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 |
Changed in mahara: | |
milestone: | 17.10.0 → 18.04.0 |
This was fixed by https:/ /bugs.launchpad .net/mahara/ +bug/1724603