…auto_increment from 100G table. No matter if it‘s InnoDB or MyISAM, you’d usually ALTER TABLE `huge_table` CHANGE `id` `id` int(6) NOT NULL and…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…make it work well such as having IO and uncompression happening in…table. This was born same as bunch of others due to statement level MySQL Replication which among other requirements, needs all auto-increment values in…
Post: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…MySQL and LAMP Consuilting Services. 2. Not using auto_increment functionality This is right. With some exception however. For example Innodb tables do internal full table…ALTER TABLE or OPTIMIZE TABLE now locks small table for few seconds rather than giant 100GB table…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…in order to remove the overhead and make it faster. By reading the rows for “INSERT“, “UPDATE“ and…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
…in order to remove the overhead and make it faster. By reading the rows for “INSERT“, “UPDATE“ and…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: 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 …,b) right which will make it faster and should not hurt any other queries a lot, right ? mysql> alter table idxitest drop key …
Comment: Spreading .ibd files across multiple disks; the optimization that isn't
December 26, 2010 By 22 Comments
…ALTER TABLE will work correctly, etc. Now consider that there are two sets of tables in the_db, transactional tables and log tables, and…table the_db.trx1(c1 int auto_increment, c2 int, c3 int, primary key(c1))engine=innodb; create table the_db.trx2(c4 int auto_increment…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…tables… Optimize For I/O It is pretty much inevitable: if you do this kind of data processing in MySQL…auto-increment primary key, and…ALTER TABLE — a typical reason to think about master-master with failover and failback for maintenance. As with anything, it…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…table containing 4 million rows and one secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT…in the table makes any difference: mysql> SET expand_fast_index_creation=OFF; Query OK, 0 rows affected (0.00 sec) mysql> ALTER TABLE…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
…auto_increment column) in nearly (emphasize nearly) every InnoDB table can have very serious and…ALTER TABLE or OPTIMIZE TABLE > now locks small table for few seconds rather than giant 100GB table…and all those changes are making it to the slave(s). Like any other decision in…
