Answers.Body: <p>Short answer: yes.</p>
  <p>As per Demerit's <a href="https://lab.civicrm.org/dev/core/-/issues/3031" rel="nofollow noreferrer">link to the</a> existing issue, it's related to a recent change in CiviCRM core that tries to be smarter when presenting the unsubscribe form.</p>
  <p>For non-obvious reasons, the changes to the sql scale badly.</p>
  <p>I've submitted a PR that seems to improve my two sites, testing and comments welcomed.</p>
  <p><a href="https://github.com/civicrm/civicrm-core/pull/23051" rel="nofollow noreferrer">https://github.com/civicrm/civicrm-core/pull/23051</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-04-01T17:27:15.590Z
Answers.Text: The PR has been merged into 5.48, thanks to you-know-who-you-are!
Answers.UserDisplayName: Alan Dixon
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-03-25T16:18:42.853Z
Answers.LastActivityDate: 2022-03-29T19:47:39.480Z
Answers.LastEditDate: 2022-03-29T19:47:39.480Z
Answers.UserDisplayName: Alan Dixon
Body: <p>When viewing the group tab of a contact, there's a &quot;smart groups&quot; openable tab, which triggers an ajax call to</p>
  <p><a href="https://example.org/civicrm/contact/view/smartgroup?cid=youcontactid&amp;snippet=json" rel="nofollow noreferrer">https://example.org/civicrm/contact/view/smartgroup?cid=youcontactid&amp;snippet=json</a></p>
  <p>After the security update of civicrm last week, two largish (100k+) installs are extremely slow when opening that tab.</p>
  <p>One example is a query time of 1000 seconds to see if a contact is in a smart group that looks for all individual contacts with a primary email address.</p>
  <p>Has anyone had any similar experiences?</p>
  <p>Here's the slow sql:</p>
  <pre><code>mysqld, Version: 10.3.28-MariaDB-1:10.3.28+maria~focal (mariadb.org binary distribution). started with:
  Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
  Time            Id Command  Argument
  # Time: 220324 19:46:19
  # User@Host: user @  [10.0.34.4]
  # Thread_id: 46884  Schema: civicrm_db  QC_hit: No
  # Query_time: 1095.429051  Lock_time: 0.000378  Rows_sent: 1  Rows_examined: 800380665
  # Rows_affected: 0  Bytes_sent: 629
  use civicrm_db;
  SET timestamp=1648151179;
  SELECT      grp.id as group_id,
                          grp.title as title,
                          grp.frontend_title as frontend_title,
                          grp.frontend_description as frontend_description,
                          grp.description as description,
                          grp.saved_search_id as saved_search_id
              FROM        civicrm_group_en_US grp
              LEFT JOIN   civicrm_group_contact gc
                  ON      gc.group_id = grp.id
              LEFT JOIN     civicrm_group_contact_cache gcc
                  ON      gcc.group_id = grp.id
              WHERE       grp.is_hidden = 0
                          AND grp.id IN (298)
                  AND     ((grp.saved_search_id is not null AND gcc.contact_id = 99609)
                              OR  (gc.contact_id = 99609
                                  AND gc.status = 'Added')
                              
                          ) GROUP BY grp.id;
  
  </code></pre>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-03-25T04:44:05.963Z
Comments.Text: Likely https://lab.civicrm.org/dev/core/-/issues/3031
Comments.UserDisplayName: Demerit
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2022-03-25T16:19:15.050Z
Comments.Text: Yes, jamie's sql is exactly the one I'm seeing in my slow query log.
Comments.UserDisplayName: Alan Dixon
Tags: <smartgroups><performance>
Title: Slow smart group display on a contact after update to 5.46.3 from 5.35.x
ThreadText: # Slow smart group display on a contact after update to 5.46.3 from 5.35.x
  
  (Asked by: username_0 on 2022-03-24)
  
  When viewing the group tab of a contact, there's a "smart groups" openable tab, which triggers an ajax call to
  https://example.org/civicrm/contact/view/smartgroup?cid=youcontactid&snippet=json
  After the security update of civicrm last week, two largish (100k+) installs are extremely slow when opening that tab.
  One example is a query time of 1000 seconds to see if a contact is in a smart group that looks for all individual contacts with a primary email address.
  Has anyone had any similar experiences?
  Here's the slow sql:
  mysqld, Version: 10.3.28-MariaDB-1:10.3.28+maria~focal (mariadb.org binary distribution). started with:
  Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
  Time            Id Command  Argument
  # Time: 220324 19:46:19
  # User@Host: user @  [10.0.34.4]
  # Thread_id: 46884  Schema: civicrm_db  QC_hit: No
  # Query_time: 1095.429051  Lock_time: 0.000378  Rows_sent: 1  Rows_examined: 800380665
  # Rows_affected: 0  Bytes_sent: 629
  use civicrm_db;
  SET timestamp=1648151179;
  SELECT      grp.id as group_id,
                          grp.title as title,
                          grp.frontend_title as frontend_title,
                          grp.frontend_description as frontend_description,
                          grp.description as description,
                          grp.saved_search_id as saved_search_id
              FROM        civicrm_group_en_US grp
              LEFT JOIN   civicrm_group_contact gc
                  ON      gc.group_id = grp.id
              LEFT JOIN     civicrm_group_contact_cache gcc
                  ON      gcc.group_id = grp.id
              WHERE       grp.is_hidden = 0
                          AND grp.id IN (298)
                  AND     ((grp.saved_search_id is not null AND gcc.contact_id = 99609)
                              OR  (gc.contact_id = 99609
                                  AND gc.status = 'Added')
                              
                          ) GROUP BY grp.id;
  
  **Tags:** smartgroups, performance
  
  > **Comments:**
  > **username_1**: Likely https://lab.civicrm.org/dev/core/-/issues/3031  
  > **username_0**: Yes, jamie's sql is exactly the one I'm seeing in my slow query log.  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_0 on 2022-03-25. Score: 2
  
  Short answer: yes.
  
  As per username_1's [link to the](https://lab.civicrm.org/dev/core/-/issues/3031) existing issue, it's related to a recent change in CiviCRM core that tries to be smarter when presenting the unsubscribe form.
  
  For non-obvious reasons, the changes to the sql scale badly.
  
  I've submitted a PR that seems to improve my two sites, testing and comments welcomed.
  
  <https://github.com/civicrm/civicrm-core/pull/23051>
  
  > **Comments:**
  > **username_0**: The PR has been merged into 5.48, thanks to you-know-who-you-are!  
  
  ---
  
  