Subject: Grouper Developers Forum
- From: "Gettes, Michael" <>
- To: "Black, Carey M." <>
- Cc: "" <>
- Subject: Re: [grouper-dev] v2.3 "slow query"
- Date: Tue, 16 Apr 2019 23:37:50 +0000
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.
> On Apr 16, 2019, at 10:43 AM, Black, Carey M. <> wrote:
> Grouper: v2.3 (latest patches)
> RDBMS: Mysql 10.2.16 ( MariaDB)
> My DBA is telling me that this query is showing up in the slow log ( this
> one is taking about 75 seconds, and happening "often" )
> Example search:
> select distinct composite2_.owner as col_0_0_, composite2_.id as col_1_0_,
> member1_.id as col_2_0_
> from grouper_memberships_all_v membership0_
> cross join grouper_members member1_
> cross join grouper_composites composite2_
> where composite2_.type='union'
> and (composite2_.left_factor=membership0_.owner_group_id or
> and membership0_.field_id='6142a7efff744cfd8a9dae4058c5d64b'
> and membership0_.immediate_mship_enabled='T'
> and membership0_.member_id=member1_.id
> and member1_.subject_source<>'g:gsa'
> and not (exists (
> select 1
> from grouper_memberships immediatem3_
> where immediatem3_.owner_group_id=composite2_.owner
> and immediatem3_.member_id=member1_.id
> and immediatem3_.mship_type='composite'
> and immediatem3_.enabled='T'
> Can you identify what in grouper is making that query?
> Is there anything that could be done to improve the performance of that
> query? ( a new index maybe? )
> Carey Matthew
- [grouper-dev] v2.3 "slow query", Black, Carey M., 04/16/2019
Archive powered by MHonArc 2.6.19.