fix: add index on order_transaction_id

[digitalgopnik]

This migration adds an index on order_transaction_id on table payone_payment_order_transaction_data.

Without the index, the query will take at least 10s (in my case). With the index set, the query will respond instantly.

Fixes the long query time when open or search an order, possibly triggered by TransactionDataHandler:getPaymentTransactionByPayoneTransactionId.

:warning: Will also occur when opening the overview of all orders!

SELECT `order_transaction`.`id` as `order_transaction.id`, `order_transaction`.`version_id` as `order_transaction.versionId`, `order_transaction`.`order_id` as `order_transaction.orderId`, `order_transaction`.`order_version_id` as `order_transaction.orderVersionId`, `order_transaction`.`payment_method_id` as `order_transaction.paymentMethodId`, `order_transaction`.`amount` as `order_transaction.amount`, `order_transaction`.`state_id` as `order_transaction.stateId`, `order_transaction.stateMachineState`.`id` as `order_transaction.stateMachineState.id`, `order_transaction.stateMachineState`.`technical_name` as `order_transaction.stateMachineState.technicalName`, `order_transaction.stateMachineState`.`state_machine_id` as `order_transaction.stateMachineState.stateMachineId`, `order_transaction.stateMachineState`.`created_at` as `order_transaction.stateMachineState.createdAt`, `order_transaction.stateMachineState`.`updated_at` as `order_transaction.stateMachineState.updatedAt`, `order_transaction.stateMachineState.translation.name`, COALESCE(`order_transaction.stateMachineState.translation.name`,`order_transaction.stateMachineState.translation.name`) as `order_transaction.stateMachineState.name`, `order_transaction.stateMachineState.translation.customFields`, COALESCE(`order_transaction.stateMachineState.translation.customFields`,`order_transaction.stateMachineState.translation.customFields`) as `order_transaction.stateMachineState.customFields`, `order_transaction.stateMachineState.translation.createdAt`, `order_transaction.stateMachineState.translation.updatedAt`, `order_transaction.stateMachineState.translation.stateMachineStateId`, `order_transaction.stateMachineState.translation.languageId`, `order_transaction`.`custom_fields` as `order_transaction.customFields`, `order_transaction`.`created_at` as `order_transaction.createdAt`, `order_transaction`.`updated_at` as `order_transaction.updatedAt`, `order_transaction.payonePaymentOrderTransactionData`.`id` as `order_transaction.payonePaymentOrderTransactionData.id`, `order_transaction.payonePaymentOrderTransactionData`.`order_transaction_id` as `order_transaction.payonePaymentOrderTransactionData.orderTransactionId`, `order_transaction.payonePaymentOrderTransactionData`.`order_transaction_version_id` as `order_transaction.payonePaymentOrderTransactionData.orderTransactionVersionId`, `order_transaction.payonePaymentOrderTransactionData`.`transaction_id` as `order_transaction.payonePaymentOrderTransactionData.transactionId`, `order_transaction.payonePaymentOrderTransactionData`.`transaction_data` as `order_transaction.payonePaymentOrderTransactionData.transactionData`, `order_transaction.payonePaymentOrderTransactionData`.`sequence_number` as `order_transaction.payonePaymentOrderTransactionData.sequenceNumber`, `order_transaction.payonePaymentOrderTransactionData`.`transaction_state` as `order_transaction.payonePaymentOrderTransactionData.transactionState`, `order_transaction.payonePaymentOrderTransactionData`.`user_id` as `order_transaction.payonePaymentOrderTransactionData.userId`, `order_transaction.payonePaymentOrderTransactionData`.`last_request` as `order_transaction.payonePaymentOrderTransactionData.lastRequest`, `order_transaction.payonePaymentOrderTransactionData`.`allow_capture` as `order_transaction.payonePaymentOrderTransactionData.allowCapture`, `order_transaction.payonePaymentOrderTransactionData`.`captured_amount` as `order_transaction.payonePaymentOrderTransactionData.capturedAmount`, `order_transaction.payonePaymentOrderTransactionData`.`allow_refund` as `order_transaction.payonePaymentOrderTransactionData.allowRefund`, `order_transaction.payonePaymentOrderTransactionData`.`refunded_amount` as `order_transaction.payonePaymentOrderTransactionData.refundedAmount`, `order_transaction.payonePaymentOrderTransactionData`.`mandate_identification` as `order_transaction.payonePaymentOrderTransactionData.mandateIdentification`, `order_transaction.payonePaymentOrderTransactionData`.`authorization_type` as `order_transaction.payonePaymentOrderTransactionData.authorizationType`, `order_transaction.payonePaymentOrderTransactionData`.`work_order_id` as `order_transaction.payonePaymentOrderTransactionData.workOrderId`, `order_transaction.payonePaymentOrderTransactionData`.`clearing_reference` as `order_transaction.payonePaymentOrderTransactionData.clearingReference`, `order_transaction.payonePaymentOrderTransactionData`.`clearing_type` as `order_transaction.payonePaymentOrderTransactionData.clearingType`, `order_transaction.payonePaymentOrderTransactionData`.`financing_type` as `order_transaction.payonePaymentOrderTransactionData.financingType`, `order_transaction.payonePaymentOrderTransactionData`.`capture_mode` as `order_transaction.payonePaymentOrderTransactionData.captureMode`, `order_transaction.payonePaymentOrderTransactionData`.`clearing_bank_account` as `order_transaction.payonePaymentOrderTransactionData.clearingBankAccount`, `order_transaction.payonePaymentOrderTransactionData`.`additional_data` as `order_transaction.payonePaymentOrderTransactionData.additionalData`, `order_transaction.payonePaymentOrderTransactionData`.`created_at` as `order_transaction.payonePaymentOrderTransactionData.createdAt`, `order_transaction.payonePaymentOrderTransactionData`.`updated_at` as `order_transaction.payonePaymentOrderTransactionData.updatedAt`, `order_transaction.paymentMethod`.`id` as `order_transaction.paymentMethod.id`, `order_transaction.paymentMethod`.`plugin_id` as `order_transaction.paymentMethod.pluginId`, `order_transaction.paymentMethod`.`handler_identifier` as `order_transaction.paymentMethod.handlerIdentifier`, `order_transaction.paymentMethod`.`position` as `order_transaction.paymentMethod.position`, `order_transaction.paymentMethod`.`active` as `order_transaction.paymentMethod.active`, `order_transaction.paymentMethod`.`after_order_enabled` as `order_transaction.paymentMethod.afterOrderEnabled`, `order_transaction.paymentMethod`.`availability_rule_id` as `order_transaction.paymentMethod.availabilityRuleId`, `order_transaction.paymentMethod`.`media_id` as `order_transaction.paymentMethod.mediaId`, `order_transaction.paymentMethod.appPaymentMethod`.`id` as `order_transaction.paymentMethod.appPaymentMethod.id`, `order_transaction.paymentMethod.appPaymentMethod`.`app_name` as `order_transaction.paymentMethod.appPaymentMethod.appName`, `order_transaction.paymentMethod.appPaymentMethod`.`identifier` as `order_transaction.paymentMethod.appPaymentMethod.identifier`, `order_transaction.paymentMethod.appPaymentMethod`.`pay_url` as `order_transaction.paymentMethod.appPaymentMethod.payUrl`, `order_transaction.paymentMethod.appPaymentMethod`.`finalize_url` as `order_transaction.paymentMethod.appPaymentMethod.finalizeUrl`, `order_transaction.paymentMethod.appPaymentMethod`.`validate_url` as `order_transaction.paymentMethod.appPaymentMethod.validateUrl`, `order_transaction.paymentMethod.appPaymentMethod`.`capture_url` as `order_transaction.paymentMethod.appPaymentMethod.captureUrl`, `order_transaction.paymentMethod.appPaymentMethod`.`refund_url` as `order_transaction.paymentMethod.appPaymentMethod.refundUrl`, `order_transaction.paymentMethod.appPaymentMethod`.`recurring_url` as `order_transaction.paymentMethod.appPaymentMethod.recurringUrl`, `order_transaction.paymentMethod.appPaymentMethod`.`app_id` as `order_transaction.paymentMethod.appPaymentMethod.appId`, `order_transaction.paymentMethod.appPaymentMethod`.`original_media_id` as `order_transaction.paymentMethod.appPaymentMethod.originalMediaId`, `order_transaction.paymentMethod.appPaymentMethod`.`payment_method_id` as `order_transaction.paymentMethod.appPaymentMethod.paymentMethodId`, `order_transaction.paymentMethod.appPaymentMethod`.`created_at` as `order_transaction.paymentMethod.appPaymentMethod.createdAt`, `order_transaction.paymentMethod.appPaymentMethod`.`updated_at` as `order_transaction.paymentMethod.appPaymentMethod.updatedAt`, `order_transaction.paymentMethod`.`technical_name` as `order_transaction.paymentMethod.technicalName`, `order_transaction.paymentMethod`.`created_at` as `order_transaction.paymentMethod.createdAt`, `order_transaction.paymentMethod`.`updated_at` as `order_transaction.paymentMethod.updatedAt`, `order_transaction.paymentMethod.translation.name`, COALESCE(`order_transaction.paymentMethod.translation.name`,`order_transaction.paymentMethod.translation.name`) as `order_transaction.paymentMethod.name`, `order_transaction.paymentMethod.translation.distinguishableName`, COALESCE(`order_transaction.paymentMethod.translation.distinguishableName`,`order_transaction.paymentMethod.translation.distinguishableName`) as `order_transaction.paymentMethod.distinguishableName`, `order_transaction.paymentMethod.translation.description`, COALESCE(`order_transaction.paymentMethod.translation.description`,`order_transaction.paymentMethod.translation.description`) as `order_transaction.paymentMethod.description`, `order_transaction.paymentMethod.translation.customFields`, COALESCE(`order_transaction.paymentMethod.translation.customFields`,`order_transaction.paymentMethod.translation.customFields`) as `order_transaction.paymentMethod.customFields`, `order_transaction.paymentMethod.translation.createdAt`, `order_transaction.paymentMethod.translation.updatedAt`, `order_transaction.paymentMethod.translation.paymentMethodId`, `order_transaction.paymentMethod.translation.languageId` FROM `order_transaction` LEFT JOIN `state_machine_state` `order_transaction.stateMachineState` ON `order_transaction`.`state_id` = `order_transaction.stateMachineState`.`id` LEFT JOIN `payone_payment_order_transaction_data` `order_transaction.payonePaymentOrderTransactionData` ON `order_transaction`.`id` = `order_transaction.payonePaymentOrderTransactionData`.`order_transaction_id` LEFT JOIN `payment_method` `order_transaction.paymentMethod` ON `order_transaction`.`payment_method_id` = `order_transaction.paymentMethod`.`id` LEFT JOIN (SELECT `order_transaction.stateMachineState.translation`.state_machine_state_id, `order_transaction.stateMachineState.translation`.name as `order_transaction.stateMachineState.translation.name`, `order_transaction.stateMachineState.translation`.custom_fields as `order_transaction.stateMachineState.translation.customFields`, `order_transaction.stateMachineState.translation`.created_at as `order_transaction.stateMachineState.translation.createdAt`, `order_transaction.stateMachineState.translation`.updated_at as `order_transaction.stateMachineState.translation.updatedAt`, `order_transaction.stateMachineState.translation`.state_machine_state_id as `order_transaction.stateMachineState.translation.stateMachineStateId`, `order_transaction.stateMachineState.translation`.language_id as `order_transaction.stateMachineState.translation.languageId` FROM `state_machine_state_translation` `order_transaction.stateMachineState.translation` WHERE `order_transaction.stateMachineState.translation`.language_id = 0x2FBB5FE2E29A4D70AA5854CE7CE3E20B) `order_transaction.stateMachineState.state_machine_state_translation` ON `order_transaction.stateMachineState.state_machine_state_translation`.`state_machine_state_id` = `order_transaction.stateMachineState`.`id` LEFT JOIN (SELECT `order_transaction.paymentMethod.translation`.payment_method_id, `order_transaction.paymentMethod.translation`.name as `order_transaction.paymentMethod.translation.name`, `order_transaction.paymentMethod.translation`.distinguishable_name as `order_transaction.paymentMethod.translation.distinguishableName`, `order_transaction.paymentMethod.translation`.description as `order_transaction.paymentMethod.translation.description`, `order_transaction.paymentMethod.translation`.custom_fields as `order_transaction.paymentMethod.translation.customFields`, `order_transaction.paymentMethod.translation`.created_at as `order_transaction.paymentMethod.translation.createdAt`, `order_transaction.paymentMethod.translation`.updated_at as `order_transaction.paymentMethod.translation.updatedAt`, `order_transaction.paymentMethod.translation`.payment_method_id as `order_transaction.paymentMethod.translation.paymentMethodId`, `order_transaction.paymentMethod.translation`.language_id as `order_transaction.paymentMethod.translation.languageId` FROM `payment_method_translation` `order_transaction.paymentMethod.translation` WHERE `order_transaction.paymentMethod.translation`.language_id = 0x2FBB5FE2E29A4D70AA5854CE7CE3E20B) `order_transaction.paymentMethod.payment_method_translation` ON `order_transaction.paymentMethod.payment_method_translation`.`payment_method_id` = `order_transaction.paymentMethod`.`id` LEFT JOIN `app_payment_method` `order_transaction.paymentMethod.appPaymentMethod` ON `order_transaction.paymentMethod`.`id` = `order_transaction.paymentMethod.appPaymentMethod`.`payment_method_id` WHERE ((`order_transaction`.`order_id` IN (0x2b9e8a8f8d9945daafe28b2038264b8a))) ORDER BY `order_transaction`.`created_at` ASC

The Entity-Extension is joined with order_transaction_id, see the screenshot below: