When viewing the group tab of a contact, there's a "smart groups" openable tab, which triggers an ajax call to


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 @  []
# 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;


1 Answer 1


Short answer: yes.

As per Demerit's link to the 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.


  • The PR has been merged into 5.48, thanks to you-know-who-you-are!
    – Alan Dixon
    Commented Apr 1, 2022 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.