Alter table Add/drop/Modify Column , Create/Drop INDEX in large database without any downtime:
MySQL 5.6: You can now perform read and write operations while an Alter Table operation is being perform even with InnoDB tables - http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html
In MySQL 5.6 and higher, the table remains available for read and write operations while the index is being created or dropped. The CREATE INDEX or DROP INDEX statement only finishes after all transactions that are accessing the table are completed, so that the initial state of the index reflects the most recent contents of the table. Previously, modifying the table while an index is being created or dropped typically resulted in a deadlock that cancelled the INSERT, UPDATE, or DELETE statement on the table.
MySQL 5.6 and higher has introduce Online DDL, this feature becomes more general: you can read and write to tables while an index is being created, and many more kinds of ALTER TABLE operations can be performed without copying the table, without blocking DML operations, or both. Thus in MySQL 5.6 and higher, we typically refer to this set of features as online DDL rather than Fast Index Creation.
Example:-
ALTER TABLE getmysql .session ADD INDEX time_idx(timestamp), ALGORITHM=INPLACE, LOCK=NONE;
ALTER TABLE `session` ADD `session_count` TINYINT(1) DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE;
No comments:
Post a Comment