MySQL Forums
Forum List  »  InnoDB

Re: Larger than expected tmp files querying InnoDB format database
Posted by: Michael Lueck
Date: August 23, 2014 08:08AM

Rick James Wrote:
-------------------------------------------------------
> > THEN go off doing the various JOIN statements
> for only those rows, that alone would work much
> faster.
>
> You got it. :)
> And that probably shrank the tmp table size
> significantly.


Ggggrrrr.... MySQL 5.5.x cannot support LIMIT within a subquery. "So close" to seeing if it would make a performance improvement.

And I gather from an Internet search that MySQL does not support Common Table Expressions (CTE's) either.

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.id IN (SELECT civicrm_contribution.id
                                  FROM civicrm_contact contact_a
                                  LEFT JOIN civicrm_contribution ON civicrm_contribution.contact_id = contact_a.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)
ORDER BY receive_date DESC,
         contact_a.id

Other suggestions?

I am thankful,

Michael Lueck
Lueck Data Systems
http://www.lueckdatasystems.com/

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Larger than expected tmp files querying InnoDB format database
1090
August 23, 2014 08:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.