… 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… stores 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=100…
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 7 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: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 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 9 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…
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…
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
… UNIQUE indexes built by sorting in case of ALTER TABLE (the big Gotcha for MyISAM tables). Are there any plans to be able…. Improved Auto Increment handling Yet another known Innodb problem is table locks which are taken for statement duration for Inserts with auto-increment column. This…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 3 Comments
… previous steps but in this case we alter every event adding DISABLE ON SLAVE. ALTER EVENT even_name DISABLE ON SLAVE. LOCAL…-22.1. AUTO INCREMENTAL VALUES In order to have the same auto incremental values on master and slaves the actual used auto incremental value is logged… and we have gaps? Example: Imagine the table has this values on the auto incremental column: 1,2,3,5 Then we execute…
