Description:
We’re experiencing a performance and correctness issue in MySQL 8.4.5 after upgrading from 8.0 where the query optimizer seems to intermittently choose a suboptimal index on the RecForm table. Here’s the setup:
CREATE TABLE `RecForm` (
`RecFormId` bigint NOT NULL AUTO_INCREMENT,
`ApplicantId` bigint NOT NULL,
`FormId` bigint NOT NULL,
`MemberId` bigint DEFAULT NULL,
`InvitationId` bigint NOT NULL,
`CreatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`SubmittedDate` datetime DEFAULT NULL,
`UpdatedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`UpdatedBy` bigint NOT NULL,
`DeleteStatus` bit(1) NOT NULL DEFAULT b'0',
`InvalidStatus` bit(1) NOT NULL DEFAULT b'0',
`PrintedDate` datetime DEFAULT NULL,
`ExportedDate` datetime DEFAULT NULL,
`SubmissionStatus` smallint DEFAULT NULL,
`ProcessStage` smallint DEFAULT NULL,
PRIMARY KEY (`RecFormId`),
UNIQUE KEY `UQ_IFM_idx` (`InvitationId`,`FormId`,`MemberId`),
KEY `FK_RecForm_ApplicantId_idx` (`ApplicantId`),
KEY `FK_RecForm_FormId_idx` (`FormId`),
KEY `FK_RecForm_MemberId_idx` (`MemberId`),
KEY `idx_SubmissionStatus_idx` (`SubmissionStatus`)
);
Problem Query:
UPDATE RecForm
SET SubmittedDate = CURRENT_TIMESTAMP, SubmissionStatus = 0
WHERE ApplicantId = 22262106
AND FormId = 23
AND MemberId = 187
AND InvitationId = 240020;
Expected Behavior:
The optimizer should prefer the composite UQ_IFM_idx (InvitationId, FormId, MemberId) to resolve the row quickly with fewer locks. And it does in Explain plan but sometimes not at runtime.
Observed Behavior:
Instead, it soemtimes uses FK_RecForm_ApplicantId_idx, which results in:
Inefficient scans
Deadlocks under concurrent workloads (see attached InnoDB status output)
But when we take explain plan, it takes correct index. Please find attached file for explain plan.
Reported bug with same kind of issue but no solution is provided, those are verified bug but not resolved
https://bugs.mysql.com/bug.php?id=108127&edit=3
Attachments:
InnoDB status log (deadlock and FK error)
Table DDL
EXPLAIN plan
How to repeat:
Reproduction Steps:
Create the RecForm table (DDL above).
Load several rows with the same ApplicantId but different MemberIds.
Run concurrent UPDATE queries similar to the one shown.
Observe InnoDB deadlocks and high lock contention.
Suggested fix:
Optimizer should always consider composite index UNIQUE KEY `UQ_IFM_idx` (`InvitationId`,`FormId`,`MemberId`). InvitationId is unique for each ApplicantId.
