GitLab

Pull request: https://github.com/civicrm/civicrm-core/pull/12740

Similar issue but not directly related: https://issues.civicrm.org/jira/browse/CRM-21811

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.

Analysis:

• When this actually times out, you get a fatal error from MySQL: "Query execution was interrupted (max_statement_time exceeded)"
• Backtrace indicates that the long-running query is the one used to generate the A-Z pager controls in search results; this query includes as join to a temporary table which is created without indices.

Proposed solution:

Add indices and column definitions to the temporary table, so that the join can use the indices and run in a timely manner.