…, execute: ALTER TABLE `test`.`test` DROP INDEX `col2`; # ######################################################################## # Summary of indexes # ######################################################################## # Size Duplicate Indexes 5 # Total Duplicate Indexes 1 # Total Indexes 3…
Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency
May 22, 2013 By Leave a Comment
… is restarted after the hot test. All OS caches are dropped at this time as well. These set of queries were… InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM tests I used a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… to the case when you’re adding/dropping/changing columns as if you just add index it will be done by sort… to the speed of ALTER TABLE, as this is not only step which ALTER TABLE does the improvement to the index creation speed itself has… having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand_fast_index_creation it…
Post: Should you name indexes while doing ALTER TABLE ?
May 28, 2008 By 10 Comments
… not require you to specify name of the index if you’re running ALTER TABLE statement – it is optional. Though what might be… you would specify index name MySQL will complain if you try to create index with same name again: mysql> alter table t1 add key… auto generated index names you may drop the wrong indexes as part of upgrade process just because somebody was adding custom indexes to the…
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… 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
… more detail. ALTER TABLE By temporarily dropping secondary indexes from the new table before copying the data, and then recreating them later, ALTER TABLE can take advantage… a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing foreign keys as if expand_fast_index_creation is OFF to avoid dropping keys that…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
… 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 j… constraint present in table “foo”. Indeed, if we try it, that’s exactly what happens: (root@localhost) [foobar]> ALTER TABLE bar drop j; ERROR…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…: ALTER TABLE `test`.`t` DROP INDEX `name_2`; # name_3 is a left-prefix of name_4 [...] # To remove this duplicate index, execute: ALTER TABLE `test`.`t` DROP INDEX `name_3`; # Key name ends with a prefix of the clustered index [...] # To shorten this duplicate clustered index, execute: ALTER TABLE…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
… generate the precise ALTER TABLE statements to drop these indexes, an exercise left to the reader. 
Estimating the size of these indexes But, what if… use innodb_file_per_table, then you can rebuild the tablespace for your table by simply doing: mysql> alter table mytable ENGINE=Innodb; However…
Post: Dropping unused indexes
January 15, 2009 By 6 Comments
… at tables which were accessed: mysql> select concat(‘alter table ‘,d.table_schema,’.',d.table_name,’ drop index ‘,group_concat(index_name separator ‘,drop index ‘),’;') stmt from (SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME FROM…
