Description:
In the below manual page,
http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html
describe about 'An ALTER TABLE statement that contains DROP INDEX and ADD INDEX clauses that both name the same index uses a table copy, not Fast Index Creation. '
But now MySQL(InnoDB) can such an operation in place (as Online DDL).
ALTER TABLE revision drop index tmp_index2, add index tmp_index2(mycol,v2,i1), algorithm=inplace, LOCK=NONE;
Query OK, 0 rows affected (6.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > select @@version;
+---------------------------------------+
| @@version |
+---------------------------------------+
| 5.6.11-enterprise-commercial-advanced |
+---------------------------------------+
1 row in set (0.00 sec)
How to repeat:
(1) prepare DDL & SP for Dummy data.
DROP TABLE IF EXISTS revision;
CREATE TABLE revision(i1 int not null primary key, v2 varchar(20)) engine = innodb;
drop procedure if exists make_revision_data;
delimiter $
create procedure make_revision_data(v_max int)
begin
declare v_id int default 0;
repeat
set v_id = v_id + 1;
insert into revision values(v_id, v_id);
if (mod(v_id,10000) = 0) then commit;
end if;
until v_id >= v_max
end repeat;
end$
delimiter ;
(2) Make dummy data
set autocommit=0;
call make_revision_data(1000000);
(3) Add an index
ALTER TABLE revision add index tmp_index2(v2,i1);
(4) Drop/Add an index
ALTER TABLE revision drop index tmp_index2, add index tmp_index2(i1,v2), algorithm=inplace, LOCK=NONE;
It can run and take 6 or 7 seconds.
If the alter table with algorithm=copy, it will take 20-seconds or moer.
Suggested fix:
Remove that limitation on MySQL 5.6 manual.
