Grouper Developers Forum

["Gettes, Michael" <>]

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.

/mrg

> On Apr 16, 2019, at 10:43 AM, Black, Carey M. <> wrote:
> 
> Using: 
> 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 
> composite2_.right_factor=membership0_.owner_group_id) 
>       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_.field_id='6142a7efff744cfd8a9dae4058c5d64b' 
>                       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 
>