CiviCRM Community Forums (archive)

We've got a customer all keen are raring to go with their new CiviCRM install but we are having a major issue with the server not coping with requests to see the CiviCRM dashboard. Apparently it is creating 3GB in the tmp directory to display their 135,000 contributions in the course of processing the query for the dashboard (I'm seeing slow performance on the search function too & it also seems to include all columns even though not all are displayed. They have quite a few custom data groups as they are using Power Base. Query at the bottom of this post

My understanding of what is happening  (which could be well out - I'm just trying to read up on MySQL optimisation now) is that because the 'receive date' criteria is in the contribution table (not the first table in the join) it can't use that criteria or the limit until it has first stored the full contents of the query in a tmp file - which it can then sort and filter to return the 10 results we want.

I'm thinking that the way it would work if it were to be optimised is that it would be two queries:

1) SELECT contribution_id FROM civicrm_contribution LEFT JOIN civicrm_contact on civicrm_contribution.contact_id = civicrm_contact.id ORDER BY receive_date WHERE contact.is_deleted =0 LIMIT 0,10

2) Run a second query getting the rest of the guff with civicrm_contribution_id IN the results of the first query




Explain results
+----+-------------+-----------------------------------------+--------+---------------------------------------------------------------------------------+-------------------------------------------------+---------+---------------------------------------------------+-------+---------------------------------+
| id | select_type | table                                   | type   | possible_keys                                                                   | key                                             | key_len | ref                                               | rows  | Extra                           |
+----+-------------+-----------------------------------------+--------+---------------------------------------------------------------------------------+-------------------------------------------------+---------+---------------------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | contact_a                               | ref    | PRIMARY,index_is_deleted                                                        | index_is_deleted                                | 1       | const                                             | 16664 | Using temporary; Using filesort |
|  1 | SIMPLE      | civicrm_contribution                    | ref    | FK_civicrm_contribution_contact_id,FK_civicrm_contribution_contribution_type_id | FK_civicrm_contribution_contact_id              | 4       | mprc_pb.contact_a.id                              |     5 | Using where                     |
|  1 | SIMPLE      | civicrm_contribution_type               | eq_ref | PRIMARY                                                                         | PRIMARY                                         | 4       | mprc_pb.civicrm_contribution.contribution_type_id |     1 |                                 |
|  1 | SIMPLE      | civicrm_contribution_product            | ref    | FK_civicrm_contribution_product_contribution_id                                 | FK_civicrm_contribution_product_contribution_id | 4       | mprc_pb.civicrm_contribution.id                   |     1 |                                 |
|  1 | SIMPLE      | civicrm_product                         | eq_ref | PRIMARY                                                                         | PRIMARY                                         | 4       | mprc_pb.civicrm_contribution_product.product_id   |     1 |                                 |
|  1 | SIMPLE      | civicrm_value_citizen_guide_12          | eq_ref | unique_entity_id                                                                | unique_entity_id                                | 4       | mprc_pb.civicrm_contribution.id                   |     1 |                                 |
|  1 | SIMPLE      | civicrm_value_contribution_data_3       | eq_ref | unique_entity_id                                                                | unique_entity_id                                | 4       | mprc_pb.civicrm_contribution.id                   |     1 |                                 |
|  1 | SIMPLE      | civicrm_value_contribution_source       | eq_ref | unique_entity_id                                                                | unique_entity_id                                | 4       | mprc_pb.civicrm_contribution.id                   |     1 |                                 |
|  1 | SIMPLE      | civicrm_value_recurring_cc_follow_up_39 | eq_ref | unique_entity_id                                                                | unique_entity_id                                | 4       | mprc_pb.civicrm_contribution.id                   |     1 |                                 |
|  1 | SIMPLE      | civicrm_note_contribution               | ref    | index_entity                                                                    | index_entity                                    | 198     | const,mprc_pb.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,index_option_group_id_name                          | index_option_group_id_name                      | 4       | mprc_pb.option_group_payment_instrument.id        |     4 |                                 |
|  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,index_option_group_id_name                          | index_option_group_id_name                      | 4       | mprc_pb.option_group_contribution_status.id       |     4 |                                 |
+----+-------------+-----------------------------------------+--------+---------------------------------------------------------------------------------+-------------------------------------------------+---------+---------------------------------------------------+-------+---------------------------------+
14 rows in set (0.03 sec)





SELECT DISTINCT(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.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.currency as `currency`,
 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`,
 civicrm_contribution.check_number as `check_number`,
 civicrm_contribution_type.id as contribution_type_id,
 civicrm_contribution_type.name as contribution_type,
 civicrm_contribution_type.accounting_code as `accounting_code`,
 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_note_contribution.note as contribution_note,
 contribution_status.label as contribution_status, payment_instrument.name
 as contribution_payment_instrument, civicrm_contribution.check_number as
 contribution_check_number, civicrm_value_contribution_data_3.id as
 civicrm_value_contribution_data_3_id,
 civicrm_value_contribution_data_3.commission_26 as custom_26,
 civicrm_value_contribution_data_3.worker_select_50 as custom_50,
 civicrm_value_citizen_guide_12.id as civicrm_value_citizen_guide_12_id,
 civicrm_value_citizen_guide_12.special_advert_requests_70 as custom_70,
 civicrm_value_citizen_guide_12.do_we_reuse_last_year_s_advert__71 as
 custom_71, civicrm_value_citizen_guide_12.any_additional_information_72 as
 custom_72, civicrm_value_citizen_guide_12.listing_73 as custom_73,
 civicrm_value_citizen_guide_12.front_or_back_page_placement_74 as
 custom_74, civicrm_value_citizen_guide_12.upload_artwork_for_advert_75 as
 custom_75, civicrm_value_contribution_source.id as
 civicrm_value_contribution_source_id,
 civicrm_value_contribution_source.campaign_source_code as custom_141,
 civicrm_value_contribution_source.campaign_method as custom_142,
 civicrm_value_contribution_data_3.commission__dollars__183 as custom_183,
 civicrm_value_contribution_data_3.donee_organization_184 as custom_184,
 civicrm_value_contribution_data_3.department_188 as custom_188,
 civicrm_value_recurring_cc_follow_up_39.id as
 civicrm_value_recurring_cc_follow_up_39_id,
 civicrm_value_recurring_cc_follow_up_39.cc_status_189 as custom_189,
 civicrm_value_recurring_cc_follow_up_39.card_no_190 as custom_190,
 civicrm_value_contribution_data_3.first_time_credit_card_196 as
 custom_196,
 civicrm_value_contribution_data_3.donation_increase_197 as custom_197
 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
 LEFT JOIN civicrm_value_citizen_guide_12 ON
 civicrm_value_citizen_guide_12.entity_id = civicrm_contribution.id
 LEFT JOIN civicrm_value_contribution_data_3 ON
 civicrm_value_contribution_data_3.entity_id = civicrm_contribution.id
 LEFT JOIN civicrm_value_contribution_source ON
 civicrm_value_contribution_source.entity_id = civicrm_contribution.id
 LEFT JOIN civicrm_value_recurring_cc_follow_up_39 ON
 civicrm_value_recurring_cc_follow_up_39.entity_id =
 civicrm_contribution.id
 LEFT JOIN civicrm_note civicrm_note_contribution ON (
 civicrm_note_contribution.entity_table = 'civicrm_contribution' AND

 civicrm_contribution.id = civicrm_note_contribution.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)   ORDER
 BY receive_date desc  LIMIT 0, 10 

(5:05:30 PM) dlobo: eileen: http://civicrm.org/node/99
(5:05:58 PM) dlobo: http://civicrm.org/blogs/lobo/optimizing-civicrm-export-stop-leaking-memory
(5:06:11 PM) eileen: off the plane?
(5:06:57 PM) dlobo: http://civicrm.org/node/343
(5:09:04 PM) eileen: dlobo - I suspect that getting the search optimised will be beyond the clients budget but they could contribute to an initiative on it - do you have an estimate?
(5:09:44 PM) dlobo: i'd put 20-40 hours
(5:10:16 PM) dlobo: kinda hard to predict those things, since the 80% of the stuff is easy, doing the final 20% is the time consuming stuff
(5:15:01 PM) cjdavis_ left the room (quit: Quit: wandered off).
(5:19:04 PM) eileen: that's like most things isn't it? Is the same optimisation basically required on contribution search, participant, member & pledge?
(5:19:58 PM) dlobo: all of them go thru the same code base, so most likely it will work across all of them
(5:20:00 PM) sushant [~sushant@114.143.18.18] entered the room.
(5:20:28 PM) eileen: So, one optimisation project could result in improvements across all those searches?
(5:23:17 PM) dlobo: we hope so
(5:23:54 PM) dlobo: its done for contact search, which also uses the same code base, so hopefully should not be much harder
(5:24:48 PM) eileen: It sounds like a good MIH candidate to me
(5:27:05 PM) dlobo: i think having a lead person for all MiH's already committed increases the chance of success
(5:27:28 PM) eileen: ?
(5:27:42 PM) dlobo: a lead sponsor
(5:27:45 PM) eileen: you mean have a sponsor on board first?
(5:28:05 PM) eileen: definitely it looks a lot botter if we're about 0%
(5:28:08 PM) eileen: when we start
(5:28:10 PM) dlobo: yeah, we have a couple in the pipeline, so hopefully they'll come thru
(5:30:52 PM) eileen: yep - I think it does help - the second de-dupe one made touch without any start-up but the first one did have a wee bit in the pot
(5:31:27 PM) eileen: I'll paste this into the forum thread in case anyone is interested in kick-starting it