Refactor the query in get_artefactchooser_artefacts to use "Union"s instead of "Or"s

[Aaron Wells]

From: https://mahara.org/interaction/forum/topic.php?id=7440&offset=0&limit=10#post29907

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[maharauser] @ [172.x.x.x]
# Query_time: 5.269038 Lock_time: 0.000127 Rows_sent: 6 Rows_examined: 684791
SET timestamp=1445254360;
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_access_role" aar ON aar.role = m.role AND aar.artefact = aa.id
WHERE m.member = '17792' AND aar.can_republish = 1
)
OR a.id IN (SELECT artefact FROM "artefact_access_usr" WHERE usr = '17792' AND can_republish = 1)
OR a.institution IN ('exampleuniversity')
) AND artefacttype IN('firstname','lastname','studentid','preferredname','introduction','officialwebsite','personalwebsite','blogadd
ress','address','town','city','country','homenumber','businessnumber','mobilenumber','faxnumber','occupation','industry','socialprofile') LIMIT
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
                              FROM artefact
                              WHERE (path = '/17' OR path LIKE '/17/%')
                              AND institution = 'mahara'
                              union
                              SELECT aar.artefact
                              FROM group_member m JOIN artefact aa
                                       ON m.group = aa.group
                                     JOIN artefact_access_role aar
                                       ON aar.role = m.role
                                       AND aar.artefact = aa.id
                              WHERE m.member = '17792'
                              AND aar.can_republish = 1
                              union
                              SELECT artefact
                              FROM artefact_access_usr
                              WHERE usr = '17792'
                              AND can_republish = 1) b
               on a.id = b.id
      union
      SELECT a.*
      FROM artefact a
      where a.institution IN ('exampleuniversity')) a
where artefacttype IN('firstname','lastname','studentid','preferredname',
                       'introduction','officialwebsite','personalwebsite','blogaddress','address',
                       'town','city','country','homenumber','businessnumber','mobilenumber',
                       'faxnumber','occupation','industry','socialprofile') limit 655360;

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.

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/5754

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/5754
Committed: https://git.mahara.org/mahara/mahara/commit/8148f07ca7304db1fdd5135d069c6a1aaf1ad3c0
Submitter: Son Nguyen (<email address hidden>)
Branch: master

commit 8148f07ca7304db1fdd5135d069c6a1aaf1ad3c0
Author: Robert Lyon <email address hidden>
Date: Mon Nov 23 14:12:23 2015 +1300

Bug 1515053: adjusting OR joins to be UNION for artefactchooser

To allow for better performance. Have taken what a user contributed
and tried to make it even more efficient still.

behatnotneeded - existing tests should take care of this

Change-Id: I91ff4e0b5e3e4e22ee437cce5e3da33bace1e736
Signed-off-by: Robert Lyon <email address hidden>