CiviCRM Community Forums (archive)

Hey all,

Where can I find the query that is built for the contribution dashboard (and other contribution queries)?  I'm researching my MySQL slow query log, and there is one query that is standing out and could use some simple modifications to improve performance, I think.  We have thousands of small contributions, so this area is hurting us in a significant way.

I'd like to see where the query is built and see if I can make the changes to that code.  I'd love a pointer, I couldn't find it by grepping for parts of the query.

The query in question is something like this (I'm not going to paste it all, it's huge):
SELECT DISTINCT(contact_a.id) as contact_id, contact_a.contact_type  as `contact_type`,
...  FROM civicrm_contact contact_a LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id  INNER JOIN civicrm_contribution_type ON civicrm_contribution.contribution_type_id = civicrm_contribution_type.id  LEFT  JOIN civicrm_contribution_product ON civicrm_contribution_product.contribution_id = civicrm_contribution.id LEFT  JOIN civicrm_product ON civicrm_contribution_product.product_id =civicrm_product.id 
...  WHERE  ( civicrm_contribution.is_test = 0 )  AND (contact_a.is_deleted = 0)   ORDER BY receive_date desc  LIMIT 0, 10;

There are two things I think we can change, and I'd like to test those out.  First, that "DISTINCT(contact_a.id)" doesn't make sense to me - I would think "DISTINCT(<field1>), <field2>" is the same as "DISTINCT <field1>, <field2>" (Distinct is meant to group all selected fields, not just a single field).  Plus, it seems unnecessary - do we really need a distinct set of rows returned in this query?  Maybe ...

Second, I'd like to move the ORDER BY and LIMIT clauses into an inline select of the civicrm_contribution table.  Having it at the end of the whole query means we join *all* rows in civicrm_contribution to all the other tables and then order the results and then limit.  I'd rather order/limit *before* joining so we only join 10 records.  I've done this just in the mysql client and it causes the query to return in less than 1 second.

I'd love to know where this is all built and would be happy to contribute a patch of some kind once I can get the new query tested.
Thanks.