[Mahara-contributors] [Bug 1161139] Re: Need performance tuning for View::search_views()
Patch for master: https://reviews.mahara.org/#/c/4272/ -- 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 ePortfolio: Confirmed 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 ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1161139] Re: Need performance tuning for View::search_views()
** Changed in: mahara Status: Confirmed => In Progress -- 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 ePortfolio: In Progress 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 ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1161139] Re: Need performance tuning for View::search_views()
** Changed in: mahara Milestone: None => 15.10.0 -- 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 ePortfolio: In Progress 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 ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1161139] Re: Need performance tuning for View::search_views()
Another patch for master: https://reviews.mahara.org/#/c/4761/ -- 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 ePortfolio: In Progress 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 ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1161139] Re: Need performance tuning for View::search_views()
** Changed in: mahara Milestone: 15.10.0 => 16.04.0 -- 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: In Progress 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 ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp
[Mahara-contributors] [Bug 1161139] Re: Need performance tuning for View::search_views()
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 ___ Mailing list: https://launchpad.net/~mahara-contributors Post to : mahara-contributors@lists.launchpad.net Unsubscribe : https://launchpad.net/~mahara-contributors More help : https://help.launchpad.net/ListHelp