…` float NOT NULL, PRIMARY KEY (`transactionid`), KEY `marketsegment` (`price`,`customerid`), KEY `registersegment` (`cashregisterid`,`price`,`customerid`), KEY `pdc` (`price`,`dateandtime`,`customerid`) ) ENGINE=InnoDB AUTO_INCREMENT=11073789 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (transactionid) (PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB, PARTITION p1…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 7 Comments
… mod http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html I have the same problem. 1. You have… my.ini file. # =0 is traditional lock mode == > funcionality of auto_increment like before 5.1.22 # =1 is default value – consecutive… 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
…(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 512000 randomly generated rows. This query uses index (type: index, key: a, key_len…
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: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… InnoDB table with auto increment column will be like this: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` int(11) DEFAULT NULL, PRIMARY KEY…` ( `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: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… | am | CREATE TABLE `am` ( `i` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`i`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST… 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… update CURRENT_TIMESTAMP, PRIMARY KEY (`url`,`thumb_width`,`thumb_height`) ) ENGINE=InnoDB; Why did I use this solution compared to others: Innodb Tables – This… is painful and great to be avoided. Long primary key – why did not I use auto_increment id in this case ? I could but…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
… with auto_increment we want to get rid of: CREATE TABLE `huge_table` ( `id` int(6) NOT NULL 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, (2…
Post: Extending Index for Innodb tables can hurt performance in a surprising way
May 22, 2010 By 9 Comments
…) unsigned NOT 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… NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `a` (`a`), KEY `a_2` (`a`,`b`) ) ENGINE=InnoDB AUTO_INCREMENT=6029313…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… 4 million rows and one secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected… dump would actually make the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed, so it…
