Group listings don't load

Kurund:

Lets do the following:

1. Add a setting called: contact_smart_group_display which has 3 values:

a - Dont show smart group accordion, i.e. suppress it completely
b - Show smart group accordion but keep it collapsed (this is default setting)
c - Show smart group accordion but open on load

Add the settings field to Customize > Display Preferences form, below 'Viewing Contacts' field.
Field Label : 'Viewing Smart Groups'
Input Widget: Radio button
Option Labels:
[ x ] Show Smart Groups on Demand
[ ] Always Show Smart Groups
[ ] Hide Smart Groups

Inline help: Controls display of the smart groups that a contact is part of in each contact's "Groups" tab. "Show on Demand" provides the best performance, and is recommended for most sites.

2. Convert the existing code base to make it ajax driven. So in cases b and c we'll load it via ajax. The default setting is b

Can you ping lcdweb when done, since he needs to add this to NYSS

Original Description:

Since the introduction of the contact_group_cache our groups pages no longer load.
The database is spinning on an insert query updating a smart group. Example query is:
INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) (SELECT 523 as group_id, contact_a.id as id FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact `civicrm_group_contact-291` ON contact_a.id = `civicrm_group_contact-291`.contact_id LEFT JOIN civicrm_group_contact `civicrm_group_contact-292` ON contact_a.id = `civicrm_group_contact-292`.contact_id LEFT JOIN civicrm_group_contact `civicrm_group_contact-293` ON contact_a.id = `civicrm_group_contact-293`.contact_id LEFT JOIN civicrm_group_contact `civicrm_group_contact-294` ON contact_a.id = `civicrm_group_contact-294`.contact_id LEFT JOIN civicrm_group_contact_cache `civicrm_group_contact_cache_519` ON contact_a.id = `civicrm_group_contact_cache_519`.contact_id WHERE ( ( `civicrm_group_contact_cache_519`.group_id = 519 AND `civicrm_group_contact-291`.group_id != ( 291 ) AND `civicrm_group_contact-291`.status IN ("Added") AND `civicrm_group_contact-292`.group_id != ( 292 ) AND `civicrm_group_contact-292`.status IN ("Added") AND `civicrm_group_contact-293`.group_id != ( 293 ) AND `civicrm_group_contact-293`.status IN ("Added") AND `civicrm_group_contact-294`.group_id != ( 294 ) AND `civicrm_group_contact-294`.status IN ("Added") AND contact_a.contact_type IN ('Individual') ) ) AND (contact_a.is_deleted = 0) AND contact_a.id NOT IN (
SELECT contact_id FROM civicrm_group_contact
WHERE civicrm_group_contact.status = 'Removed'
AND civicrm_group_contact.group_id = 523 ) )

According to Eileen group hierarchies are expensive to resolve; especially with smart groups that have relationships as is the case in the example above.

Upon any access to groups e.g. group tab on a contact; group listing; civimail mailing, the cache gets invalidated and rebuilt. The query to rebuild this takes longer than the page timeouts (multiple minutes for the above query) thus rendering the site unusable. This group structure works fine on 4.1