Query speed issue searching on transaction ID

On upgrading to 4.6 we are experiencing very slow speeds on searching on transaction id from contribution search. email has also been reported as a new slow field for contribution search.

The immediate reason for this is the search query has been changed from an indexed where clause

contribution_trxn_id = '123'

To an unindexed clause - ie

contribution_trxn_id = '%123%'

The difficulty here is that for large sites this is a serious performance impediment. But, for small sites it is a possible enhancement (ie. they get a like search without having to remember to add a '%' sign.)

This is not the first instance of this type of change and the others were made as part of code simplification rather than performance - so the first thing to figure out is whether we can/ should revert these query changes or whether we need to find a way to allow sites to be able to choose between apparent flexibility or performance. (I say apparent as it is actually less flexible since before they could choose to use a like or an = - but I accept that the experience of the user will dictate this perception.)

@monishdeb - can you comment in the first instance on your perception of the history of these changes - I'll discuss with Dave tomorrow.