Evaluate if any indexed fields are unused

Proposal (note this is being updated based on discussion & comments may refer to an earlier version)

1. Remove the following columns from the xml from civicrm_activity

• phone_id
• phone_number
• relationship_id

1. Remove the index from the xml on

• medium_id
• is_deleted

1. During upgrade we drop the above columns, if empty.

Follow ups to consider

2. There are other columns in the civicrm_activity table that are case specific - we might consider indexing is_current_revision & original_id only when CiviCase is enabled

3. I've been doing some tests on searches and found that searching is faster if I DROP the contribution_status_id index - it might be interesting to test the activity_type_id index although I suspect it has a much greater cardinality & is more useful

Impact of the above

1. Data would not be lost but api fields would no longer access those fields
2. Developers who might be using them outside of core could be impacted - we can mitigate by communicating on the dev list & perhaps putting checks & deprecation notices onto sites with data in the fields for a few months before making any changes.
3. DB size would be reduced. Note that empty fields contribute notably to table size IF they are indexed
4. Dev confusion & efficiency is improved by not having unused stuff in core.

Background Obviously that's not something we should rush into so I'll have to ping the dev list etc

Looking at our civicrm_activity table it appears that each index has a base size - of around a half a gig. From there, the index size increases based on how much data is in the table. So an index on an empty field is around 57% of the size of our largest index.

There are 5 fields that are indexed + empty in our database for the civicrm_activity table (

Original id  used for CiviCase
Medium id
Phone id
relationship_id 
is_deleted 

Plus - is_current_revision is effectively null

So my first question is are these all used in other databases - e.g when civicase is in use.

I couldn't spot references to phone_id and it feels 'wrong' to me anyway as I think you would want to either link to the contact or have a hard reference. I wonder if some of these fields are quietly obsolete?

It's very unsafe to drop core fields. However, I'm pondering dropping the indexes on these fields

@DaveD I'd appreciate your thoughts....