[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
