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)
))));
One improvement would be particularly easy. Each time you call the function, it actually executes two queries. The first is a count(*) to get the total number of results that match your search criteria, and the second is to get some portion of those results (using ORDER BY and LIMIT). Some places which call this function don't need the count(*) results, so we could change the function so that returning the count(*) is optional, and that would reduce some executions of the query.
But, it would still be good if we could get the performance of the query improved. Part of the problem, though, is that there are just a whole bunch of views in a big Mahara site. Perhaps adding some additional indices would help...