…> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE test\G… second ALTER will also succeed -and the index will be created-, but we will get a warning (note severity, to be exact): mysql> ALTER TABLE test ADD INDEX (col2); Query…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… any more. Creating Index by sort is great Creating index by sort can provide over 20x performance improvement to the speed of ALTER TABLE, as this is not only step which ALTER TABLE…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
…, for example: mysql> create table t1(i int, j int); Query OK, 0 rows affected (0.01 sec) mysql> alter table t1 add key… index name ? MySQL will happily create (and maintain) as many duplicate keys as you like without even giving you a warning: mysql> alter table… if you would specify index name MySQL will complain if you try to create index with same name again: mysql> alter table t1 add key…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table…TABLES; Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table…
Post: Why ALTER TABLE shows as two transactions in SHOW ENGINE INNODB STATUS
July 1, 2012 By 4 Comments
When executing an ALTER TABLE, InnoDB (and XtraDB) will create two InnoDB transactions: One transaction is created when the table being ALTERed is locked by the… table `schema`.`table_name` trx id XXXX lock mode S” in SHOW ENGINE INNODB STATUS. Another is created when adding or dropping an index…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… the fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX. Peter… indexes in ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing…
Post: Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
… you find it? (Hint: think about indexes.) … Notice in each SQL statement the clause: FORCE INDEX(`guest_language`) The tool has chosen…_ins`; Dropping new table… DROP TABLE IF EXISTS `test`.`_t_new`; Dropped new table OK. Notice that the new, altered table created at the beginning is…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
… see this example: mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> show create table t\G [...] KEY `name… create duplicate indexes: mysql> alter table t add index key_for_first_name(name); Query OK, 0 rows affected (0.01 sec) mysql> alter table t add index…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
… tables: CREATE TABLE foo ( i INT NOT NULL PRIMARY KEY, j INT NOT NULL, INDEX(j), FOREIGN KEY (j) REFERENCES bar(j) ) ENGINE=INNODB; CREATE TABLE bar ( i INT NOT NULL PRIMARY KEY, j INT NOT NULL, INDEX(j) ) ENGINE=INNODB; trying to do something like “ALTER TABLE bar DROP j” or “ALTER TABLE bar MODIFY COLUMN j…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…ALTER TABLE statements to drop these indexes, an exercise left to the reader. 
Estimating the size of these indexes…
