Deadlocks on group_cache clearing

We have been experiencing significant issues with Deadlocks on the group contact cache table since upgrading from 4.4 to 4.6.

I don't think the issue I'm reporting here is new to 4.6 - I think the issue is the previous work-around is broken & hence I'm looking at the next issue along the chain.

On 4.4 our heaviest group user effectively disabled smart group caching because it caused more performance problems than it solved. However, in 4.6 this doesn't seem to work because using TRUNCATE breaks civimail in scary & nasty ways - so setting smart group timeout to 0 can trigger this :

if (!isset($groupID)) {
if ($smartGroupCacheTimeout == 0) {
$query = "
TRUNCATE civicrm_group_contact_cache
";

However, looking at why the query causes such severe outcome there are 2 issues

1) the degree of locking
2) the speed of the query.

The locking issue appears to be that when any of the queries intended to update the group_contact cache table or relevant fields in the group table run they lock the entire table (s) rather than the rows due to lack of indexes.

This query

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

Does a 'WHERE' on the unindexed field 'cache_date'

According to this link http://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html

" Rows are locked during updates using indexes. For example,

update tab set col1=3 where col2=17;

will lock the entire table unless col2 is indexed (in which case, only rows where col2=17 will be locked)."

(the comment is old but someone has commented to the same effect later on)

The columns that I believe should be indexed (and converted to timestamp) are

civicrm_acl_cache.modified_date
civicrm_group.cache_date
civicrm_group.refresh_date

Given that we are experiencing considerable instability around group caching on 4.6 I would want this indexes to be included in the 4.6 LTS. However, we'll do some live testing of adding the indexes first.

I'll post the sql we are using to this ticket.