Details
-
Bug
-
Resolution: Fixed
-
Minor
-
2.4.0.patch, 2.4.1
-
None
Description
The total member count query for visualization is:
select gg.id,
|
(
|
select count(distinct gmlv.member_id)
|
from grouper_memberships_lw_v gmlv
|
where gmlv.group_id = gg.id
|
and gmlv.list_name = 'members'
|
/* and gmlv.subject_source != 'g:gsa' */
|
) as total_membership_count
|
from grouper_groups gg
|
where gg.id in (...)
|
The performance of this query in MySQL is poor with a large number of memberships in the database. With the help of Michael Gettes, the issue seems to be related to the use of grouper_memberships_lw_v, which itself depends on another view grouper_memberships_all_v. The use of nested views seems to be the cause, since rewriting the query to use grouper_memberships_all_v directly has much better performance:
select gg.id,
|
(
|
select count(distinct gms.member_id)
|
from grouper_memberships_all_v gms, grouper_members gm, grouper_fields gfl
|
where gms.owner_group_id = gg.id
|
and gms.field_id = gfl.id
|
and gms.member_id = gm.id
|
and gfl.name = 'members'
|
/* and gm.subject_source != 'g:gsa' */
|
) as total_membership_count_2
|
from grouper_groups gg
|
where gg.id in (...)
|