Bug #118083 optimize for invisible indexes ddl
Submitted: 29 Apr 2025 9:36 Modified: 2 Jun 5:26
Reporter: alex xing (OCA) Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: DDL Severity:S5 (Performance)
Version:8.0.42 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[29 Apr 2025 9:36] alex xing 
Description:
Optimize DDL execution for invisible indexes by reducing traversal from twice to once

Previously, the native logic would traverse the index_visibility_list twice when handling DDL operations related to invisible indexes. This commit refactors the relevant logic to complete the operation with just a single traversal.

How to repeat:
just read the code

Suggested fix:
optimize as the below patch
[29 Apr 2025 9:36] alex xing 
a simple patch to describe the optimization

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: alter_invisible_index_optimize.patch (application/octet-stream, text), 1.97 KiB.
[29 Apr 2025 10:24] MySQL Verification Team 
Hello Alex Xing,

Thank you for the report and contribution.

regards,
umesh
[2 Jun 5:26] Prabakaran Thirumalai 
Hi Alex,

The code change is logically sound because it avoids a second scan of alter_info->alter_index_visibility_list without altering the existing behavior.

This code path runs only once during ALTER TABLE preparation, and ALTER INDEX ... VISIBLE/INVISIBLE lists are usually very small. As a result, the expected performance improvement is negligible. The overall cost of ALTER TABLE is still dominated by storage-engine operations, data dictionary updates, and any required table rebuilds.

Thanks
Prabakaran

For these reasons, we are not moving forward with this patch.