The requirement for this index is 'implied' in the xml as
However, this doesn't result in an actual index being created. I think we need a function to add these as required as I'm not sure we can be sure that no clever person thought to create them on any table covered by this.
Note that the key order should start with the more unique - ie. entity_id for more effective searching.
I think it might be worth adding another line to the spec of an index
Where this exists and is set to 1 then the upgrade script will add it - where it does not exist or is not set to 1 it must be added to the sql. This allows us to create an index reconciliation script without removing upgrade writers ability to be more specific. This would allow 4.6 or earlier users to add indexes to their installs knowing they won't break on 4.7 or later. OR, importantly to pre-add indexes in preparation for upgrades. I say this because on larger sites I often remove slow parts of the upgrade script and pre-run them prior to the upgrade to reduce the upgrade outage time, and to reduce the time it takes to do upgrade testing.
I'm not sure if the proposed granular upgrade idea completely mitigates these issues and whether it can be introduced in a way that will allow those on 4.6 or earlier to benefit from it.