… all tables, respecting AUTO_INCREMENT set for the Merge Table itself. Neither of these expectations really true: mysql> create table a1(i int unsigned not null auto_increment primary… how stable merge table auto_increment values are by truncating the table we just inserted data to: mysql> truncate table a2; ERROR 1105 (HY000): MyISAM table ‘a2′ is…
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: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
…. 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… it locking relaxed in latest MySQL 5.0 Hopefully the patches he has provided will be merged soon if not we should…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…large data sets are very expensive. If MySQL supported sort-merge or hash joins, you’d have other possibilities… Especially on huge tables, it lets you scan portions of a table instead of the whole table if you a…key as (day, ad). Don’t use an auto-increment primary key, and don’t put ad first.Â…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… can do the following: mysql> CREATE TABLE `test2` ( -> `c` char(255) DEFAULT NULL, -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id… innodb_force_recovery modes to block purging activity, insert buffer merge or recovery from transactional logs all together. Though the lower…
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… inserted in the correct order into sequentially allocated pages after merge-sorting. So besides optimizing DDL directly, expand_fast_index_creation…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
…mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `c1` int(11) DEFAULT NULL, `c2` int(11) NOT NULL AUTO_INCREMENT…
Comment: Spreading .ibd files across multiple disks; the optimization that isn't
December 26, 2010 By 22 Comments
… a VIEW (using the MERGE algorithm) which simulates a database ‘SYNONYM’: create table the_db.trx1(c1 int auto_increment, c2 int, c3 int… SQL. The MERGE view algorithm will ensure that this doesn’t create performance problems. MySQL will simply access the base table (`fast_ssd…
Comment: MySQL Installation and upgrade scripts.
May 21, 2007 By 3 Comments
… auto_increment values in the replication binlogs (bug #24432) 5.0.36sp1 – slow, with “check for upgrade” repairs, no problems with large merge tables… – slow, with “check for upgrade” repairs, problems with merge tables, performance problems while table is updated by PK = empty string (actially is was… present time I decided to upgrade to 5.0.36sp1 (MySQL Enperprise) it seems to be the most stable release, it…
