mahara-contributors team mailing list archive

[Kristina Hoeppner <1161139@xxxxxxxxxxxxxxxxxx>]

The change for https://reviews.mahara.org/#/c/4272/ was released in
Mahara 15.04 by the looks of it. If anything else needs to be don, a new
bug should be opened.

The other patch was abandoned.

** Changed in: mahara
       Status: In Progress => Fix Released

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

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

Title:
  Need performance tuning for View::search_views()

Status in Mahara:
  Fix Released

Bug description:
  This is a core function in lib/view.php, and it generates a monster
  SQL statement that finds all the views that can be seen by an
  individual user. The exact details of the query depend on the
  parameters sent to the function, but in any form it causes big
  performance problems once you hit the size of myportfolio.school.nz.
  Here is a sample of the query:

              SELECT count(*)
  	            FROM "view" v
  	            LEFT OUTER JOIN "collection_view" cv ON cv.view = v.id
  	            LEFT OUTER JOIN "collection" c ON cv.collection = c.id
  	            LEFT OUTER JOIN (
  	                SELECT c.onview, MAX(a.mtime) AS lastcomment
  	                FROM "artefact_comment_comment" c JOIN "artefact" a ON c.artefact = a.id AND c.deletedby IS NULL AND c.private = 0
  	                GROUP BY c.onview
  	            ) l ON v.id = l.onview
  	            
  	            WHERE (v.owner IS NULL OR v.owner > 0)
  	                AND (v.group IS NULL OR v.group NOT IN (SELECT id FROM "group" WHERE deleted = 1)) AND v.type IN ('portfolio')
  	                AND (FALSE
  	                    OR ( -- user has permission to see the view
  	                        (v.startdate IS NULL OR v.startdate < current_timestamp)
  	                        AND (v.stopdate IS NULL OR v.stopdate > current_timestamp)
  	                        AND (v.id IN ( -- user access
  	                                SELECT va.view
  	                                FROM "view_access" va
  	                                WHERE va.usr =  102159
  	                                    AND (va.startdate IS NULL OR va.startdate < current_timestamp)
  	                                    AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
  	                            ) OR v.id IN ( -- friend access
  	                                SELECT va.view
  	                                FROM "view_access" va
  	                                    JOIN "view" vf ON va.view = vf.id AND vf.owner IS NOT NULL
  	                                    JOIN "usr_friend" f ON ((f.usr1 =  102159 AND f.usr2 = vf.owner) OR (f.usr1 = vf.owner AND f.usr2 =  102159))
  	                                WHERE va.accesstype = 'friends'
  	                                    AND (va.startdate IS NULL OR va.startdate < current_timestamp)
  	                                    AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
  	                            ) OR v.id IN ( -- group access
  	                                SELECT va.view
  	                                FROM "view_access" va
  	                                    JOIN "group_member" m ON va.group = m.group AND (va.role = m.role OR va.role IS NULL)
  	                                WHERE
  	                                    m.member =  102159
  	                                    AND (va.startdate IS NULL OR va.startdate < current_timestamp)
  	                                    AND (va.stopdate IS NULL OR va.stopdate > current_timestamp)
  	                            ))));

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1161139/+subscriptions