Uploaded image for project: 'Grouper'
  1. Grouper
  2. GRP-2246

Visualization: tune group count query for better performance in MySQL

    XMLWordPrintable

Details

    • Bug
    • Status: Resolved
    • Minor
    • Resolution: Fixed
    • 2.4.0.patch, 2.4.1
    • 2.4.0.patch, 2.5.0, 2.4.1
    • API
    • 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 (...)
      

      Attachments

        Activity

          People

            chad.redman@at.internet2.edu Chad Redman (unc.edu)
            chad.redman@at.internet2.edu Chad Redman (unc.edu)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Smart Checklist