Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-21811

Optimize advanced search by relationship with target group for reciprocal relationship types

    Details

    • Type: Improvement
    • Status: Done/Fixed
    • Priority: Minor
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.7.30, 4.7.31
    • Fix Version/s: 5.3
    • Labels:
      None
    • Versioning Impact:
      Patch (backwards-compatible bug fixes)
    • Documentation Required?:
      None
    • Funding Source:
      Contributed Code
    • Verified?:
      No

      Description

      On many sites, this is not an issue, but the more "spouse" relationships a site has, the more likely it happens (and not just spouse; this applies for "partner of" and any other reciprocally named relationship type).

      Steps to reproduce:

      1. Start with a site having:
        1. Several thousand active relationships of a reciprocal type (where the name for both related positions is the same – e.g., "spouse of" <=> "spouse of"; "sibling of" <=> "sibling of"; "partner of" <=> "partner of"). This recipe uses "Spouse of".
        2. At least one group (smart group or static group).
      2. Perform an Advanced Search with these criteria:
        1. Relationships > Relationship Type: "Spouse of"
        2. Relationships > Target Contact(s) in Group: [any one or more groups]
      3. Submit the search form and observe a very long page load; depending on the number of relationships, server configuration, and server resources, this search may end in a WSOD, or take dozens of minutes to complete.

      Proposed solution:

      • Expand existing code constructs, already in use for reciprocal relationship types in Relationships > Relationship Type when Relationships > Target Contact(s) in Group is undefined. That is, create a temporary table using UNION to flatten reciprocal relationships and avoid using OR in joins (because OR in a join will prevent MySQL from using indexes in that join).

        Attachments

          Activity

            People

            • Assignee:
              allenshaw Allen Shaw
              Reporter:
              allenshaw Allen Shaw
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: