Author Topic: civicrm_group_contact_cache - extra join? (Read 1017 times)

Eileen · May 09, 2011, 09:02:53 pm

Hi,

This is all over our slow query log. I don't think the query itself is the reason but looking at it I can't for the life or me see why the join to the contact table is required here

DELETE g
FROM civicrm_group_contact_cache g
INNER JOIN civicrm_contact c ON c.id = g.contact_id
WHERE g.group_id IN (
SELECT id
FROM civicrm_group
WHERE TIMESTAMPDIFF(MINUTE, cache_date, $now) >= $smartGroupCacheTimeout
)
";

Donald Lobo · May 09, 2011, 09:58:08 pm

that query looks mighty odd. can u give this one a try instead:

Code: [Select]

DELETE     gc                                                                                                                                
FROM       civicrm_group_contact_cache gc                                                                                                   
INNER JOIN civicrm_group g ON g.id = gc.group_id                                                                                          
WHERE      TIMESTAMPDIFF(MINUTE, g.cache_date, $now) >= $smartGroupCacheTimeout

Eileen · May 09, 2011, 10:00:02 pm

Will try - nb query is in

CRM_Contact_BAO_GroupContactCache::store

Eileen · May 10, 2011, 06:03:29 pm

I think you mean this

DELETE gc
FROM civicrm_group_contact_cache gc
INNER JOIN civicrm_group g ON g.id = gc.group_id
WHERE TIMESTAMPDIFF(MINUTE, g.cache_date, $now) >= $smartGroupCacheTimeout

Can you update your post so no-one ever copies it & uses it - delete from cache table not groups table

Eileen · May 10, 2011, 06:21:52 pm

Also is seems to me that when $config->smartGroupCacheTimeout is not set then the whole query should be

Code: [Select]

DELETE     g
FROM       civicrm_group_contact_cache g

(Or Truncate civicrm_group_contact_cache)

Current code sets $smartGroupCacheTimeout to null & runs the complex query below rather than running a different query if it isn't set

Code: [Select]

        $smartGroupCacheTimeout = 
            isset( $config->smartGroupCacheTimeout ) && is_numeric(  $config->smartGroupCacheTimeout ) ? $config->smartGroupCacheTimeout : 0;

        if ( ! isset( $groupID ) ) {
            $query = "
DELETE     g
FROM       civicrm_group_contact_cache g
INNER JOIN civicrm_contact c ON c.id = g.contact_id
WHERE      g.group_id IN (
    SELECT id
    FROM   civicrm_group
    WHERE  TIMESTAMPDIFF(MINUTE, cache_date, $now) >= $smartGroupCacheTimeout   
)
";

CiviCRM Community Forums (archive)

News:

Author Topic: civicrm_group_contact_cache - extra join? (Read 1017 times)

Eileen

civicrm_group_contact_cache - extra join?

Donald Lobo

Re: civicrm_group_contact_cache - extra join?

Eileen

Re: civicrm_group_contact_cache - extra join?

Eileen

Re: civicrm_group_contact_cache - extra join?

Eileen

Re: civicrm_group_contact_cache - extra join?