What's the best way to optimize a joined update?

Question

I have a long-running MySQL query that updates one table's records from their corresponding records in another table. In this case setting fields from the derived merchant assignment record onto the original transaction record:

update transaction t
        inner join assignment a on a.transaction_id = t.id
    set
        t.merchant_name = a.name,
        t.merchant_org_id = a.merchant_org_id,
        t.category_id = a.category_id
        t.assignment_pending = false
        where t.id in ('...', '...', '...')

This query is taking... a very long time. I have an index on the assignment's transaction_id column... but is there another way of optimizing this query?

Below is the relevant schema and explain plan:

CREATE TABLE `transaction` (
  `id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `org_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `service_connection_id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `amount` bigint NOT NULL,
  `currency_code` varchar(3) NOT NULL,
  `amount_usd` bigint NOT NULL DEFAULT '0',
  `merchant_name` varchar(255) NOT NULL,
  `mcc` char(4) DEFAULT NULL,
  `transaction_type` varchar(255) NOT NULL,
  `category_id` char(36) NOT NULL,
  `merchant_org_id` char(36) DEFAULT NULL,
  `user_id` char(36) DEFAULT NULL,
  `mask` varchar(255) DEFAULT NULL,
  `assignment_pending` tinyint(1) NOT NULL DEFAULT '0',
  `state` varchar(255) NOT NULL DEFAULT 'posted',
  `assignment_id` varchar(255) DEFAULT NULL,
  `epoch_authorized` bigint NOT NULL DEFAULT '0',
  `epoch_posted` bigint DEFAULT NULL,
  `raw_description` varchar(255) NOT NULL DEFAULT '',
  `created` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_provider_tx_id` (`org_id`,`provider_tx_id`),
  KEY `idx_tx_merchant` (`merchant_org_id`),
  KEY `idx_tx_assignment` (`assignment_id`),
  KEY `idx_tx_org_authorized` (`org_id`,`epoch_authorized`),
  KEY `idx_tx_org_posted` (`org_id`,`epoch_posted`),
  KEY `idx_tx_org_merchant` (`org_id`,`merchant_org_id`),
  KEY `idx_tx_org_amount_usd` (`org_id`,`amount_usd`),
  KEY `idx_tx_assignment_pending` (`org_id`,`assignment_pending`,`epoch_authorized`,`id`),
  KEY `idx_tx_org_vendor_by_range` (`org_id`,`merchant_org_id`,`epoch_authorized`)
)

CREATE TABLE `assignment` (
  `id` char(36) NOT NULL,
  `org_id` char(36) NOT NULL,
  `user_id` char(36) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `date` date DEFAULT NULL,
  `factor_id` char(36) DEFAULT NULL,
  `merchant_org_id` char(36) DEFAULT NULL,
  `category_id` char(36) NOT NULL,
  `transaction_id` char(36) DEFAULT NULL,
  `assignment_method` varchar(255) NOT NULL,
  `created` bigint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_assignment_transaction` (`transaction_id`),
  KEY `idx_org` (`org_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_date` (`date`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Explain Plan

ID	1
Select type	UPDATE
Table	t
Matching partitions
Join type	const
Chosen index	PRIMARY
Chosen index length	144
Compared columns	const
Rows filtered	100%
Query cost	0
Additional information

ID	1
Select type	SIMPLE
Table	a
Matching partitions
Join type	range
Chosen index	idx_assignment_transaction
Chosen index length	145
Compared columns
Rows filtered	100%
Query cost	0
Additional information	Using where

Answer 1

@ErgestBasha guessed correctly. The problem was the collation. For context, I don't use Foreign Keys for application reasons (reluctant to think about ordering when bulk creating records) so that's probably how I ended up in this situation.

Once I ensured the ID fields were collated correctly and in the same manner as their corresponding key columns, the update query dropped from tens of seconds to fractions of a second.

-- Example of manually setting the collation method on the desired columns

ALTER TABLE transaction MODIFY COLUMN id CHAR(36) NOT NULL COLLATE utf8mb4_bin;
ALTER TABLE transaction MODIFY COLUMN assignment_id CHAR(36) NULL COLLATE utf8mb4_bin;
ALTER TABLE assignment MODIFY COLUMN id CHAR(36) NOT NULL COLLATE utf8mb4_bin;
ALTER TABLE assignment MODIFY COLUMN transaction_id CHAR(36) NULL COLLATE utf8mb4_bin;