Rick James Wrote:
-------------------------------------------------------
> > What do you make of it?
>
> Nothing yet, because:
> > Warning: Server has not been running for at
> least 48hrs.
> > It may not be safe to use these recommendations
I would have thought the query would use considerable less tmp disk space on account of using the subquery to limit the rows to be joined by the main query. I bounce MySQL to flush the query cache to get an accurate picture of what the query will do worst case scenario.
> > IN (SELECT ... LIMIT 0, 10)
>
> Don't use that construct. Instead, JOIN to that
> subquery.
>
> JOIN (SELECT ... LIMIT 0, 10) x ON x.id =
> civicrm_contribution.id
>
> Performance will improve significantly and I think
> you can avoid "cannot support LIMIT within a
> subquery".
OK, thank you. I reworked it as follows, it does now execute, returns the correct rows, *** and not a bit faster nor uses less tmp disk space. :-( ***
> Using 1- or 2-letter aliases for table names would
> make the query less cluttered.
I am not up to that extensive of modificaitons to project code. I was trying to be a proxy to offer/contribute back a better way to write queries in general. Not looking so hot presently.
Query is now 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 (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) x ON x.id = civicrm_contribution.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 )
ORDER BY receive_date DESC,
contact_a.id
I am thankful,
Michael Lueck
Lueck Data Systems
http://www.lueckdatasystems.com/