Refactor the query in get_artefactchooser_artefacts to use "Union"s instead of "Or"s
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
Mahara |
Fix Released
|
Medium
|
Unassigned |
Bug Description
From: https:/
We have a mahara install with about 40k users (200/11k daily/all-time active) and are facing performance issues which we think are related to the nature of the queries created by mahara.
Database size: 544.8MB
Disk usage: 50.3GB
Mahara version: 1.10.2
Typical slow query :
# User@Host: maharauser[
# Query_time: 5.269038 Lock_time: 0.000127 Rows_sent: 6 Rows_examined: 684791
SET timestamp=
SELECT a.*, (a.owner IS NOT NULL AND a.owner = '17792') AS editable FROM "artefact" a WHERE (
a.owner = '17792'
OR a.id IN (
SELECT id
FROM "artefact"
WHERE (path = '/17' OR path LIKE '/17/%') AND institution = 'mahara'
)
OR a.id IN (
SELECT aar.artefact
FROM "group_member" m
JOIN "artefact" aa ON m.group = aa.group
JOIN "artefact_
WHERE m.member = '17792' AND aar.can_republish = 1
)
OR a.id IN (SELECT artefact FROM "artefact_
OR a.institution IN ('exampleuniver
) AND artefacttype IN('firstname'
ress','
655360;
our dba, after considering the usual optimization cache/buffer size and re-indexing techniques has suggested an equivalent query :
select a.*,
(a.owner IS NOT NULL AND a.owner = '17792') AS editable
from (SELECT a.*
FROM artefact a
WHERE a.owner = '17792'
union
SELECT a.*
FROM artefact a join (SELECT id
on a.id = b.id
union
SELECT a.*
FROM artefact a
where a.institution IN ('exampleuniver
where artefacttype IN('firstname'
which takes 0.263 seconds. The mysql database version we are running on is Server version: 5.1.73
We have replicated on Server version: 5.5.41-MariaDB on the same dataset. Although the gains were not as dramatic from 1.2 seconds to 0.1 seconds. Even so total page load times are an important usability consideration.
Changed in mahara: | |
status: | Confirmed → Fix Committed |
Changed in mahara: | |
status: | Fix Committed → Fix Released |
Patch for "master" branch: https:/ /reviews. mahara. org/5754