… follows: CREATE TABLE `purchases_noindex` ( `transactionid` int(11) NOT NULL AUTO_INCREMENT, `dateandtime` datetime DEFAULT NULL, `cashregisterid` int(11) NOT NULL, `customerid…=InnoDB AUTO_INCREMENT=11073789 DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE (transactionid) (PARTITION p0 VALUES LESS THAN (100000000) ENGINE = InnoDB, PARTITION p1 VALUES LESS…
Comment: Benchmarking single-row insert performance on Amazon EC2
May 16, 2012 By 11 Comments
…’t measuring the cost of index maintenance. Transaction-id is auto-incremented, and so partitioning on transaction-id makes the insertions easy… simply because you are writing 4 times fewer B-tree values. The primary key and each secondary key each incur nearly… rows is about 30GB. Since we are inserting on an auto-increment key, I would expect Inno to fill the B-tree…
Comment: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
March 11, 2012 By 7 Comments
… http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html I have the same problem. 1. You have… is traditional lock mode == > funcionality of auto_increment like before 5.1.22 # =1 is default value – consecutive lock mode # =2 is interleaved… primary key name – is unique varchar insert into tab (name) values (‘Peter’),(‘Oto’),(‘Jan’),(‘Jan’),(‘Jan’),(‘Romco’); select * from tab order…
Post: Sharing an auto_increment value across multiple MySQL tables (revisited)
October 26, 2010 By 7 Comments
A couple of weeks ago I blogged about Sharing an auto_increment value across multiple MySQL tables. In the comments, a few people…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… row will cause auto_increment value to be reused too. Can you use AUTO_INCREMENT clause in CREATE TABLE to get different auto_increment values ? I guess not… stores auto_increment value (and of course does it without any warnings) Neither setting auto_increment value for underlying MyISAM tables works: mysql> alter table a1 auto_increment…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… gaps on auto-increment columns? Secondly, I will show you a trick to mimic the INSERT IGNORE behaviour without losing auto increment values. Let’s start! Why do we have gaps? InnoDB checks an auto_increment counter on the table and if a new value… the auto_increment value is configurable and you can select from different algorithms using the innodb_autoinc_lock_mode. By default the value is…
Post: Sharing an auto_increment value across multiple MySQL tables
October 4, 2010 By 9 Comments
… not null primary key auto_increment) engine=innodb; # each insert does one operations to get the value: INSERT INTO option1 VALUES (NULL); # $connection->insert… a new sequence value insert into another table The testing options are: Option 1 – auto_commit each statement Option 2 – auto_commit each… you have already started modifying data before you need an auto_increment number but you do not want to commit yet. Full…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… count exact number of auto increment values needed or if we get behavior change by having potential “holes” when auto increment values are assigned but never… number of rows in auto-increment batch insert is not known as we do not have to allocate sequential auto increment values if row level…
Post: Statement based replication with Stored Functions, Triggers and Events
December 16, 2011 By 3 Comments
….17-22.1. AUTO INCREMENTAL VALUES In order to have the same auto incremental values on master and slaves the actual used auto incremental value is logged as an…); Binary Log: The value 4 is inserted with the INSERT statement and the INSERT_ID is the next auto incremental value: #111214 21:54…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
… Max_data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2008-04-24 00:41:01 Update… Max_data_length: 1970324836974591 Index_length: 1024 Data_free: 0 Auto_increment: NULL Create_time: 2008-04-24 01:14:06 Update… convenient to work with. Using NULL as one of flag values means you can’t use normal “=” comparison operator with them…
