MySQL Bugs: #118481: MySQL 8.4.5 chooses suboptimal index for UPDATE query, leading to deadlocks and performance degradation

Bug #118481	MySQL 8.4.5 chooses suboptimal index for UPDATE query, leading to deadlocks and performance degradation
Submitted:	18 Jun 20:04	Modified:	3 Aug 18:30
Reporter:	Rajshree Nema	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	Mysql 8.4.5	OS:	Linux (Amazon Linux (AWS RDS managed))
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	Optimizer bug

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.

Hi,

Optimizer is always considering it. It can just decide not to use it always.

I can't reproduce the problem using my generated data. Can you provide data (like for Bug #108127) that makes this reproducible?

Thanks

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

could also be useful to see outputs of :

select count(*),
 count(distinct ApplicantId),
 sum(ApplicantId = 22262106),
 
 count(distinct FormId),
 sum(FormId = 23),
 
 count(distinct MemberId),
 sum(MemberId = 187),
 
 count(distinct InvitationId),
 sum(InvitationId = 240020),
 
 sum(ApplicantId = 22262106 AND FormId = 23 AND MemberId = 187 AND InvitationId = 240020),
 sum(ApplicantId = 22262106 AND FormId = 23),
 sum(ApplicantId = 22262106 AND MemberId = 187),
 sum(ApplicantId = 22262106 AND InvitationId = 240020),
 
 sum(FormId = 23 AND MemberId = 187),
 sum(FormId = 23 AND MemberId = 187 AND InvitationId = 240020),
 sum(FormId = 23 AND InvitationId = 240020),
 sum(MemberId = 187 AND InvitationId = 240020),
 sum(ApplicantId = 22262106 AND MemberId = 187 AND InvitationId = 240020)
 
 from RecForm \G

show indexes from 'RecForm';