… Create Table: CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col2` int(11) DEFAULT NULL, `col3` varchar(200) DEFAULT NULL… null # `id` int(11) not null auto_increment # To shorten this duplicate clustered index, execute: ALTER TABLE `test`.`test` DROP INDEX `redundant`, ADD…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…. Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not: mysql> alter table am auto_increment=1000; Query OK… auto_increment value (and of course does it without any warnings) Neither setting auto_increment value for underlying MyISAM tables works: mysql> alter table a1 auto_increment…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… IGNORE behaviour without losing auto increment values. Let’s start! Why do we have gaps? InnoDB checks an auto_increment counter on the table…(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET… customer. So, if this saves you from doing an ALTER TABLE to change the auto incremental column size, then send him a beer 
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… is to remove current PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and… files. When and how it works: auto_increment (removing). Let’s have a simple table with auto_increment we want to get rid of…
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… performance improvement to the speed of ALTER TABLE, as this is not only step which ALTER TABLE does the improvement to the… but with index built by sort enabled the performance of ALTER TABLE with 128MB was better than with 6GB. It looks…
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
…. It works well when all you want is to remove auto_increment or change ENUM values. When it comes to changes that… are assigned to db1 at this stage. If you’re altering gigabyte size tables, this step may take some time. Go…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… cases when this optimization is not applicable: UNIQUE indexes in ALTER TABLE are ignored to enforce uniqueness where necessary when copying… the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed, so it is impossible…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… its making changes to the table metadata, a table ALTER that alters a big table can keep executing without any hindrance, copying… events: session1 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; Query OK, 0 rows affected (0…_timeout=1;CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB; However, I feel that the CREATE…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…auto_increment_incrementblogpercona.commanual auto_increment…alter_db_upgradeblogpercona.commanual Com_alter_eventblogpercona.commanual Com_alter_functionblogpercona.commanual Com_alter_procedureblogpercona.commanual Com_alter_serverblogpercona.commanual Com_alter_tableblogpercona.commanual Com_alter…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… ? Will be UNIQUE indexes built by sorting in case of ALTER TABLE (the big Gotcha for MyISAM tables). Are there any… count exact number of auto increment values needed or if we get behavior change by having potential “holes” when auto increment values are assigned… number of rows in auto-increment batch insert is not known as we do not have to allocate sequential auto increment values if row…
