Bug #119294 add new column along with indexing on it should be INSTANT in mysql table

• Submitted: 3 Nov 14:33
• Reporter: Zafar Malik
• Status: Open
• Category: MySQL Server: General
• Severity: S4 (Feature request)
• Version: 8.4.4 (Any)
• OS: Any
• CPU Architecture: Any
• Tags: alter column, MySQL

[3 Nov 14:33] Zafar Malik

Description:
Right now ALGORITHM=INSTANT works if we add a new column but it is not working if we are adding a new column along with indexing on that new column.

Ideally as there is no data in new column, so there is no need of any sorting of data, so addition of a new column and indexing on it should allow ALGORITHM=INSTANT.

Thanks,
Zafar

How to repeat:
#create a test table
CREATE TABLE TEST1(id int AUTO_INCREMENT, f_name varchar(50), PRIMARY KEY(id));

Case1: Add a new column with ALGORITHM=INSTANT
ALTER TABLE TEST1 ADD COLUMN l_name varchar(50), ALGORITHM=INSTANT;

Result: Succeed

Case2: Add a new column along with indexing on it with ALGORITHM=INSTANT
ALTER TABLE TEST1 ADD COLUMN l_name varchar(50), ADD KEY idx_lname(l_name), ALGORITHM=INSTANT;

Result: ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

Suggested fix:
As there is no data in new column, so there is no need of any sorting of data, so addition of a new column and indexing on it should allow ALGORITHM=INSTANT.