… 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… 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
… 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… table to be rebuilt, while things like removing auto_increment or changing a default value still do even though this information is stored in table…
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: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…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: 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…+——–+———-+———-+——————————————————————-+ 2 rows in set (4 min 5.52 sec) mysql> alter table…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select count(*) from idxitest where a=5 and b=5; +———-+ | count(*) | +———-+ | 60434 | +———-+ 1 row in… lot, right ? mysql> alter table idxitest drop key a…CREATE TABLE `idxitest` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `a`…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…auto_increment functionality This is right. With some exception however. For example Innodb tables do internal full table lock if auto_increment…ALTER TABLE or OPTIMIZE TABLE now locks small table for few seconds rather than giant 100GB table… using innodb_file_per_table option in particular. 4. Not …
Post: Using MMM to ALTER huge tables
March 27, 2008 By 11 Comments
…to remove auto_increment or change ENUM values. When it comes to changes that really require table to be … (current active master) and db2 (standby master) in it. Precautions: Before applying any changes, you …db1 at this stage. If you’re altering gigabyte size tables, this step may take some time. …
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
… that index provided by InnoDB were left in a bogus state until an explicit ANALYZE TABLE is executed. #857590 (Alexey Kopytov). QUERY… failure in InnoDB. #814404 (Yasufumi Kinoshita). Since AUTO_INCREMENT columns must be defined as keys, omitting key specifications and then adding them back in ALTER TABLE…
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… that index provided by InnoDB were left in a bogus state until an explicit ANALYZE TABLE is executed. #857590 (Alexey Kopytov). QUERY… failure in InnoDB. #814404 (Yasufumi Kinoshita). Since AUTO_INCREMENT columns must be defined as keys, omitting key specifications and then adding them back in ALTER TABLE…
