…: 0 mysql> SHOW CREATE TABLE test\G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col2` int(11) DEFAULT… null # `id` int(11) not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `test`.`test` DROP INDEX `redundant`, ADD INDEX…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… stable merge table auto_increment values are by truncating the table we just inserted data to: mysql> truncate table a2; ERROR 1105 (HY000): MyISAM table ‘a2… auto_increment value to be reused too. Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not: mysql> alter table am auto_increment…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… want to remove auto_increment from 100G table. No matter if it’s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id… remove current PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take time to…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
…) Now check the auto_increment counter: show create table foo\G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int… a customer. So, if this saves you from doing an ALTER TABLE to change the auto incremental column size, then send him a beer 
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
… Max_data_length: 0 Index_length: 1460322304 Data_free: 7340032 Auto_increment: 10000001 Create_time: 2012-06-27 13:04:56 Update… 20x performance improvement to the speed of ALTER TABLE, as this is not only step which ALTER TABLE does the improvement to the index…: If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable expand…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 18 Comments
…TABLE `a` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c` char(64) DEFAULT NULL, PRIMARY KEY (`id`), KEY `c` (`c`) ) ENGINE=InnoDB AUTO_INCREMENT… ALTER TABLE which requires table rebuild. Dropping all indexes; doing ALTER and when adding them back can be a lot faster than straight ALTER TABLE…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 10 Comments
… when this is not the case. CREATE TABLE `idxitest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b… should not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK… but it well often does not: CREATE TABLE `idxitest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b…
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
… way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that really require table to be rebuilt – adding… assigned to db1 at this stage. If you’re altering gigabyte size tables, this step may take some time. Go get a…
Comment: Hacking to make ALTER TABLE online for certain changes
December 3, 2007 By 26 Comments
Nice post guys, I was trying to do this trick to actually change the auto_incremenet and it didn’t work 
Anyone knows how to do it without running “ALTER TABLENAME auto_increment = ….” because it takes about 6 hours on my table with 56,000,00 rows and 10 Indexes …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…’s start with a table containing 4 million rows and one secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c… ALTER TABLE after restoring the data from a dump would actually make the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT…
