Performance Issue counting Contributions Contact summary

Whilst looking at performance problems at LLR, came across the contributionCount method in the CRM_Contribute_BAO_Contribution class.

Its query is something like this

SELECT count( contribution.id ) count
FROM civicrm_contribution contribution LEFT JOIN civicrm_contribution_soft softContribution
ON ( contribution.id = softContribution.contribution_id )
WHERE contribution.is_test = 0 AND ( contribution.contact_id = 742 OR softContribution.contact_id = 742 OR contribution.honor_contact_id = 742 ) and 2 = 2;
-------

-------

-------
1 rows in set (3.17 sec)

This was based on a around 1.7 million contribution records.

Due to the 'OR' statements the wasn't using any indexes on the contribution table.

In our install we use this query instead

SELECT count( x.id ) count FROM (
SELECT contribution.id AS id
FROM civicrm_contribution contribution
WHERE contribution.is_test = 0 AND contribution.contact_id = 742
UNION
SELECT contribution.id
FROM civicrm_contribution contribution
WHERE contribution.is_test = 0 AND contribution.honor_contact_id = 742
UNION
SELECT contribution.id
FROM civicrm_contribution contribution INNER JOIN civicrm_contribution_soft softContribution
ON ( contribution.id = softContribution.contribution_id )
WHERE contribution.is_test = 0 AND softContribution.contact_id = 742) x
-------

-------

-------
1 rows in set (0.07 sec)

So my feeling is this is a much more efficient query.

Code for method is below.

function contributionCount( $contactId, $includeSoftCredit = true, $includeHonoree = true )
{
if ( !$contactId ) return 0;
$contactContributionsSQL = "SELECT contribution.id AS id
FROM civicrm_contribution contribution
WHERE contribution.is_test = 0 AND contribution.contact_id = {$contactId} ";

$contactHonoreeContributionsSQL = "SELECT contribution.id
FROM civicrm_contribution contribution
WHERE contribution.is_test = 0 AND contribution.honor_contact_id = {$contactId} ";

$contactSoftCreditContributionsSQL = "SELECT contribution.id
FROM civicrm_contribution contribution INNER JOIN civicrm_contribution_soft softContribution
ON ( contribution.id = softContribution.contribution_id )
WHERE contribution.is_test = 0 AND softContribution.contact_id = {$contactId} ";
$query = "SELECT count( x.id ) count FROM ( ";
$query .= $contactContributionsSQL;
if ( $includeSoftCredit )

{ $query .= " UNION "; $query .= $contactSoftCreditContributionsSQL; }

if ( $includeHonoree )

{ $query .= " UNION "; $query .= $contactHonoreeContributionsSQL; }

$query .= ") x";

return CRM_Core_DAO::singleValueQuery( $query );
}