…PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`) ) ENGINE=InnoDB AUTO_INCREMENT…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 7 Comments
Dear Miguel mysql from 5.1.22 change autoincrement mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html I… investigate what it do innodb_autoinc_lock_mode=0 2. restart mysql server now is working perfectly id – is primary key name – is unique…
Comment: What does Using filesort mean in MySQL?
March 15, 2012 By 27 Comments
… reason why MySQL does not use index in the following scenario: CREATE TABLE table1 ( id bigint(20) NOT NULL AUTO_INCREMENT, a varchar(100) DEFAULT NULL, b varchar(100) DEFAULT NULL, PRIMARY KEY (id), KEY (a), KEY (b) ) ENGINE=InnoDB Table 1 has…
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… ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 No…
Post: Sharing an auto_increment value across multiple MySQL tables
October 4, 2010 By 9 Comments
… the insert_id: CREATE TABLE option1 (id int not null primary key auto_increment) engine=innodb; # each insert does one operations to get the value… one just row: CREATE TABLE option2 (id int not null primary key) engine=innodb; INSERT INTO option2 VALUES (1); # start from 1 # each…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
…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 key…or for good you should remember auto_increment for Merge Tables works differently from both MyISAM and Innodb tables and being similar to…
Post: Long PRIMARY KEY for Innodb tables
October 3, 2006 By 8 Comments
… PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys… in case of MySQL/System crash is painful and great to be avoided. Long primary key – why did not I use auto_increment id in…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… either created, or last refeshed. The incremental method is somewhat conceptually similar to using mysql binary logs for point-in-time recovery… gets a special column `mview$pk` which is an auto_increment BIGINT surrogate key for the table. For complete refresh views which are… ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> select mview$pk as rank, customer_id, total…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… InnoDB auto-inc scalability issue with MySQL 5.0 and older versions, employing other techniques to maintain the PK auto incremental …auto_increment, `text` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB To remove auto_increment, we (1) create table with the same layout but without auto_increment,…
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…` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `a_2` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313 DEFAULT CHARSET=latin1 mysql> select * from idxitest where…
