Quote
I will post further once I have completed the upgrade to current stable on the new / "test" server. BRB...
I am back with the sad results based on running the latest stable version of CiviCRM: v4.4.8. No improvement. Query and explain as follows:
SELECT civicrm_contribution.id,
contact_a.id AS contact_id,
contact_a.contact_type AS `contact_type`,
contact_a.contact_sub_type AS `contact_sub_type`,
contact_a.sort_name AS `sort_name`,
contact_a.display_name AS `display_name`,
civicrm_contribution.id AS contribution_id,
civicrm_contribution.currency AS `currency`,
civicrm_contribution.receive_date AS `receive_date`,
civicrm_contribution.non_deductible_amount AS `non_deductible_amount`,
civicrm_contribution.total_amount AS `total_amount`,
civicrm_contribution.fee_amount AS `fee_amount`,
civicrm_contribution.net_amount AS `net_amount`,
civicrm_contribution.trxn_id AS `trxn_id`,
civicrm_contribution.invoice_id AS `invoice_id`,
civicrm_contribution.cancel_date AS `cancel_date`,
civicrm_contribution.cancel_reason AS `cancel_reason`,
civicrm_contribution.receipt_date AS `receipt_date`,
civicrm_contribution.thankyou_date AS `thankyou_date`,
civicrm_contribution.source AS `contribution_source`,
civicrm_contribution.amount_level AS `amount_level`,
civicrm_contribution.is_test AS `is_test`,
civicrm_contribution.is_pay_later AS `is_pay_later`,
contribution_status.value AS contribution_status_id,
civicrm_contribution.check_number AS `check_number`,
civicrm_contribution.campaign_id AS contribution_campaign_id,
civicrm_financial_type.id AS financial_type_id,
civicrm_financial_type.name AS financial_type,
payment_instrument.id AS instrument_id,
payment_instrument.label AS `payment_instrument`,
civicrm_product.id AS product_id,
civicrm_product.name AS `product_name`,
civicrm_product.sku AS `sku`,
civicrm_contribution_product.id AS contribution_product_id,
civicrm_contribution_product.product_option AS `product_option`,
civicrm_contribution_product.fulfilled_date AS `fulfilled_date`,
civicrm_contribution_product.start_date AS `contribution_start_date`,
civicrm_contribution_product.end_date AS `contribution_end_date`,
civicrm_contribution.contribution_recur_id AS `contribution_recur_id`,
civicrm_financial_account.id AS financial_account_id,
civicrm_financial_account.accounting_code AS accounting_code,
civicrm_note.note AS contribution_note,
civicrm_batch.title AS contribution_batch,
contribution_status.label AS contribution_status,
payment_instrument.name AS contribution_payment_instrument,
civicrm_contribution.check_number AS contribution_check_number
FROM civicrm_contact contact_a
LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.id
INNER JOIN civicrm_financial_type ON civicrm_contribution.financial_type_id = civicrm_financial_type.id
LEFT JOIN civicrm_entity_financial_account ON civicrm_entity_financial_account.entity_id = civicrm_contribution.financial_type_id
AND civicrm_entity_financial_account.entity_table = 'civicrm_financial_type'
INNER JOIN civicrm_financial_account ON civicrm_financial_account.id = civicrm_entity_financial_account.financial_account_id
INNER JOIN civicrm_option_value cov ON cov.value = civicrm_entity_financial_account.account_relationship
AND cov.name = 'Income Account is'
INNER JOIN civicrm_option_group cog ON cog.id = cov.option_group_id
AND cog.name = 'account_relationship'
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
LEFT JOIN civicrm_entity_financial_trxn ON (civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'
AND civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id )
LEFT JOIN civicrm_financial_trxn ON (civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id )
LEFT JOIN civicrm_entity_batch ON ( civicrm_entity_batch.entity_table = 'civicrm_financial_trxn'
AND civicrm_financial_trxn.id = civicrm_entity_batch.entity_id )
LEFT JOIN civicrm_batch ON civicrm_entity_batch.batch_id = civicrm_batch.id
LEFT JOIN civicrm_note ON ( civicrm_note.entity_table = 'civicrm_contribution'
AND civicrm_contribution.id = civicrm_note.entity_id )
LEFT JOIN civicrm_option_group option_group_payment_instrument ON ( option_group_payment_instrument.name = 'payment_instrument')
LEFT JOIN civicrm_option_value payment_instrument ON (civicrm_contribution.payment_instrument_id = payment_instrument.value
AND option_group_payment_instrument.id = payment_instrument.option_group_id )
LEFT JOIN civicrm_option_group option_group_contribution_status ON (option_group_contribution_status.name = 'contribution_status') LEFT JOIN civicrm_option_value contribution_status ON (civicrm_contribution.contribution_status_id = contribution_status.value
AND option_group_contribution_status.id = contribution_status.option_group_id )
WHERE (civicrm_contribution.is_test = 0)
AND (contact_a.is_deleted = 0)
GROUP BY civicrm_contribution.id
ORDER BY receive_date DESC,
contact_a.id
LIMIT 0, 10;
+----+-------------+----------------------------------+--------+------------------------------------------------------------------------------------------------+----------------------------------------------------------+---------+--------------------------------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------------+--------+------------------------------------------------------------------------------------------------+----------------------------------------------------------+---------+--------------------------------------------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | cog | const | PRIMARY,UI_name | UI_name | 194 | const | 1 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | cov | ref | index_option_group_id_value,index_option_group_id_name,FK_civicrm_option_value_option_group_id | index_option_group_id_name | 391 | const,const | 1 | Using where |
| 1 | SIMPLE | civicrm_financial_account | ALL | PRIMARY | NULL | NULL | NULL | 12 | Using join buffer |
| 1 | SIMPLE | civicrm_entity_financial_account | ref | FK_civicrm_entity_financial_account_financial_account_id | FK_civicrm_entity_financial_account_financial_account_id | 4 | micc_civicrm.civicrm_financial_account.id | 2 | Using where |
| 1 | SIMPLE | civicrm_contribution | ref | FK_civicrm_contribution_contact_id,FK_civicrm_contribution_financial_type_id | FK_civicrm_contribution_financial_type_id | 5 | micc_civicrm.civicrm_entity_financial_account.entity_id | 25489 | Using where |
| 1 | SIMPLE | contact_a | eq_ref | PRIMARY,index_is_deleted_sort_name | PRIMARY | 4 | micc_civicrm.civicrm_contribution.contact_id | 1 | Using where |
| 1 | SIMPLE | civicrm_financial_type | eq_ref | PRIMARY,UI_id | PRIMARY | 4 | micc_civicrm.civicrm_entity_financial_account.entity_id | 1 | |
| 1 | SIMPLE | civicrm_contribution_product | ref | FK_civicrm_contribution_product_contribution_id | FK_civicrm_contribution_product_contribution_id | 4 | micc_civicrm.civicrm_contribution.id | 1 | |
| 1 | SIMPLE | civicrm_product | eq_ref | PRIMARY | PRIMARY | 4 | micc_civicrm.civicrm_contribution_product.product_id | 1 | |
| 1 | SIMPLE | civicrm_entity_financial_trxn | ref | UI_entity_financial_trxn_entity_table,UI_entity_financial_trxn_entity_id | UI_entity_financial_trxn_entity_id | 4 | micc_civicrm.civicrm_contribution.id | 1 | |
| 1 | SIMPLE | civicrm_financial_trxn | eq_ref | PRIMARY | PRIMARY | 4 | micc_civicrm.civicrm_entity_financial_trxn.financial_trxn_id | 1 | Using index |
| 1 | SIMPLE | civicrm_entity_batch | ref | index_entity | index_entity | 199 | const,micc_civicrm.civicrm_financial_trxn.id | 1 | |
| 1 | SIMPLE | civicrm_batch | eq_ref | PRIMARY | PRIMARY | 4 | micc_civicrm.civicrm_entity_batch.batch_id | 1 | |
| 1 | SIMPLE | civicrm_note | ref | index_entity | index_entity | 198 | const,micc_civicrm.civicrm_contribution.id | 1 | |
| 1 | SIMPLE | option_group_payment_instrument | const | UI_name | UI_name | 194 | const | 1 | Using index |
| 1 | SIMPLE | payment_instrument | ref | index_option_group_id_value,FK_civicrm_option_value_option_group_id | FK_civicrm_option_value_option_group_id | 4 | micc_civicrm.option_group_payment_instrument.id | 6 | |
| 1 | SIMPLE | option_group_contribution_status | const | UI_name | UI_name | 194 | const | 1 | Using index |
| 1 | SIMPLE | contribution_status | ref | index_option_group_id_value,FK_civicrm_option_value_option_group_id | FK_civicrm_option_value_option_group_id | 4 | micc_civicrm.option_group_contribution_status.id | 6 | |
+----+-------------+----------------------------------+--------+------------------------------------------------------------------------------------------------+----------------------------------------------------------+---------+--------------------------------------------------------------+-------+----------------------------------------------+
Michael Lueck
Lueck Data Systems
http://www.lueckdatasystems.com/