Upgrade problem large DB on unindexed temp table

On large DB this left join is too slow

LEFT JOIN civicrm_temp_14e975573383fbcb34da0f815840e3f8 tpi // Joe - this is around lines 374 - 375

need to add an index - the full query is from

CRM_Upgrade_Incremental_php_FourThree::createFinancialRecords()

Surprisingly the failure happens later with a table lock problem - implying the queue runner is not waiting. I had to restart the queue runner at an earlier point too when it had succeeded.

Note that I have already spent twice as long on this upgrade as the customer is paying for so can talk to the customer about supplying a copy but I don't want to spend more time on testing etc.

CREATE TEMPORARY TABLE civicrm_temp_eb8c732022b71ed8e5311af2e3ddeefa

SELECT con.id as contribution_id, con.payment_instrument_id, IF(con.currency IN ('AFN','ALL','DZD','USD','EUR','AOA','XCD','XCD','ARS','AMD','AWG','AUD','EUR','AZM','BSD','BHD','BDT','BBD','BYR','EUR','BZD','XOF','BMD','INR','BTN','BOB','BOV','BAM','BWP','NOK','BRL','USD','BND','BGN','XOF','BIF','KHR','XAF','CAD','CVE','KYD','XAF','XAF','CLP','CLF','CNY','AUD','AUD','COP','COU','KMF','XAF','CDF','NZD','CRC','XOF','HRK','CUP','CYP','CZK','DKK','DJF','XCD','DOP','USD','EGP','SVC','USD','XAF','ERN','EEK','ETB','FKP','DKK','FJD','EUR','EUR','EUR','XPF','EUR','XAF','GMD','GEL','EUR','GHC','GIP','EUR','DKK','XCD','EUR','USD','GTQ','GNF','GWP','XOF','GYD','HTG','USD','AUD','EUR','HNL','HKD','HUF','ISK','INR','IDR','XDR','IRR','IQD','EUR','ILS','EUR','JMD','JPY','JOD','KZT','KES','AUD','KPW','KRW','KWD','KGS','LAK','LVL','LBP','ZAR','LSL','LRD','LYD','CHF','LTL','EUR','MOP','MKD','MGA','MWK','MYR','MVR','XOF','MTL','USD','EUR','MRO','MUR','EUR','MXN','MXV','USD','MDL','EUR','MNT','XCD','MAD','MZM','MMK','ZAR','NAD','AUD','NPR','EUR','ANG','XPF','NZD','NIO','XOF','NGN','NZD','AUD','USD','NOK','OMR','PKR','USD','PAB','USD','PGK','PYG','PEN','PHP','NZD','PLN','EUR','USD','QAR','EUR','ROL','RON','RUB','RWF','SHP','XCD','XCD','EUR','XCD','WST','EUR','STD','SAR','XOF','CSD','EUR','SCR','SLL','SGD','SKK','SIT','SBD','SOS','ZAR','EUR','LKR','SDD','SRD','NOK','SZL','SEK','CHF','CHW','CHE','SYP','TWD','TJS','TZS','THB','USD','XOF','NZD','TOP','TTD','TND','TRY','TRL','TMM','USD','AUD','UGX','UAH','AED','GBP','USD','USS','USN','USD','UYU','UZS','VUV','VEB','VND','USD','USD','XPF','MAD','YER','ZMK','ZWD','XAU','XBA','XBB','XBC','XBD','XPD','XPT','XAG','XFU','XFO','XTS','XXX'), con.currency, 'AUD') as currency,
con.total_amount, con.net_amount, con.fee_amount, con.trxn_id, con.contribution_status_id, con.check_number, NULL as from_financial_account_id,
REPLACE(REPLACE(REPLACE(
CASE
WHEN con.receive_date IS NOT NULL THEN
con.receive_date
WHEN con.receipt_date IS NOT NULL THEN
con.receipt_date
ELSE
20130318190928
END
, '-', ''), ':', ''), ' ', '') as trxn_date,
CASE
WHEN con.payment_instrument_id IS NULL THEN
29
WHEN con.payment_instrument_id IS NOT NULL THEN
tpi.financial_account_id
END as to_financial_account_id,
IF(eft.financial_trxn_id IS NULL, 'insert', eft.financial_trxn_id) as action
FROM civicrm_contribution con
LEFT JOIN civicrm_entity_financial_trxn eft
ON (eft.entity_table = 'civicrm_contribution' AND eft.entity_id = con.id)
LEFT JOIN civicrm_temp_14e975573383fbcb34da0f815840e3f8 tpi
ON con.payment_instrument_id = tpi.instrument_id
WHERE con.contribution_status_id IN (1, 3)