…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
…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
…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
…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
…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
…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
…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
…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
…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
…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…

