Subject: Grouper Developers Forum
- From: Robert Bradley <>
- Subject: Re: [grouper-dev] v2.3 "slow query"
- Date: Wed, 17 Apr 2019 16:34:04 +0100
-----BEGIN PGP SIGNED MESSAGE-----
On 17/04/2019 00:37, Gettes, Michael wrote:
| Carey, do you have a large number of composite groups?
| Grouper_composites.type isn’t indexed. You could try and index it
| and see if that speeds things up.
| Additionally, the query has a problem which I have already
| identified in GRP-2080 where “exists” in MySQL servers can be a wee
| bit slow. Rewritten (in this case) as “and not (1 in (select 1…”
| would yield much faster response and works equally well on Oracle.
| Seems like we need to have an evaluation of all uses of exists in
| queries. Also, the "subject_source <> ‘g:gsa’" should be rewritten
| as “not subject_source = ‘g:gsa’”.
| Exactly where in grouper this query is being done - I am not sure
| but this is what I recommend as a fix for us MySQL users.
This sounds a lot like the issues I saw with Grouper and Postgres 9.1
a while back:
If building from source is an option, I'd try applying the changes
from that post and see what difference (if any) it makes. Extra
indexes are probably a good thing too. My extra indexes for this
particular query currently look like:
- - (owner_id, field_id)
- - (member_id, enabled, owner_id, field_id)
If the problem's with an actual "union" composite group though, I'd
try replacing it with a normal group with two group members.
- -- Dr Robert Bradley
Identity and Access Management Team, IT Services, University of Oxford
-----BEGIN PGP SIGNATURE-----
-----END PGP SIGNATURE-----
- [grouper-dev] v2.3 "slow query", Black, Carey M., 04/16/2019
Archive powered by MHonArc 2.6.19.