… cause 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… MyISAM tables works: mysql> alter table a1 auto_increment=100; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> alter table a2 auto_increment=100…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… you using InnoDB tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A…) 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: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…PK auto_incremental from the table. As a rule of thumb, this usually involves altering huge InnoDB tables and huge tables take…auto_increment, (2) flush tables with read lock, (3) swap .frm files while keeping mysql suspended and (4) unlock the tables afterwards: — 1. mysql> CREATE TABLE `huge_table…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…auto_increment_incrementblogpercona.commanual auto_increment…
Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
December 9, 2010 By 13 Comments
…mysql> alter table a drop key c; Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize table a; +——–+———-+———-+——————————————————————-+ | Table…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
… NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from idxitest where a=5 and… and should not hurt any other queries a lot, right ? mysql> alter table idxitest drop key a,add key(a,b); Query OK…
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 FLOAT… trigger table rebuilds without affecting the table size. mysql> SET expand_fast_index_creation=ON; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE…
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… same table. This was born same as bunch of others due to statement level MySQL Replication which among other requirements, needs all auto-increment…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 3 Comments
… case we alter every event adding DISABLE ON SLAVE. ALTER EVENT even_name…auto incremental values on master and slaves the actual used auto incremental value is logged as an extra event just before the statement. MySQL… Example: Imagine the table has this values on the auto incremental column: 1,2,…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… offering MySQL and LAMP Consuilting Services. 2. Not using auto_increment functionality This is right. With some exception however. For example Innodb tables …data becomes managable, your ALTER TABLE or OPTIMIZE TABLE now locks small table for few seconds rather than giant 100GB table for few hours …
