auto_increment Archives - Percona Database Performance Blog

Store UUID in an optimized way

Karthik Appigatla | December 19, 2014 | Posted In: MySQL

A few years ago Peter Zaitsev, in a post titled “To UUID or not to UUID,” wrote: “There is timestamp based part in UUID which has similar properties to auto_increment and which could be used to have values generated at same point in time physically local in BTREE index.” For this post I’ve rearranged the timestamp part […]

Multi-Valued INSERTs, AUTO_INCREMENT & Percona XtraDB Cluster

Ernie Souhrada | September 9, 2014 | Posted In: Insight for DBAs, MySQL, Percona XtraDB Cluster

A common migration path from standalone MySQL/Percona Server to a Percona XtraDB Cluster (PXC) environment involves some measure of time where one node in the new cluster has been configured as a slave of the production master that the cluster is slated to replace. In this way, the new cluster acts as a slave of […]

Sharing an auto_increment value across multiple MySQL tables (revisited)

Morgan Tocker | October 26, 2010 | Posted In: Benchmarks, Insight for Developers, MySQL

A couple of weeks ago I blogged about Sharing an auto_increment value across multiple MySQL tables. In the comments, a few people wrote in to suggest alternative ways of implementing this.Â I just got around to benchmarking those alternatives today across two large EC2 machines:

Sharing an auto_increment value across multiple MySQL tables

Morgan Tocker | October 4, 2010 | Posted In: Benchmarks, Insight for Developers, MySQL

The title is SEO bait – you can’t do it. We’ve seen a few recurring patterns trying to achieve similar – and I thought I would share with you my favorite two: Option #1: Use a table to insert into, and grab 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:
INSERT INTO option1 VALUES (NULL);
# $connection->insert_id();

CREATE TABLE option1 (id int not null primary key auto_increment) engine=innodb;

# each insert does one operations to get the value:

INSERT INTO option1 VALUES (NULL);

# $connection->insert_id();

Option #2: Use a table with one just row:

CREATE TABLE option2 (id int not null primary key) engine=innodb;
INSERT INTO option2 VALUES (1); # start from 1
# each insert does two operations to get the value:
UPDATE option2 SET id=@id:=id+1;
SELECT @id;

CREATE TABLE option2 (id int not null primary key) engine=innodb;

INSERT INTO option2 VALUES (1); # start from 1

# each insert does two operations to get the value:

UPDATE option2 SET id=@id:=id+1;

SELECT @id;

Store UUID in an optimized way

Multi-Valued INSERTs, AUTO_INCREMENT & Percona XtraDB Cluster

Sharing an auto_increment value across multiple MySQL tables (revisited)

Sharing an auto_increment value across multiple MySQL tables

Upcoming Webinars

PERCONA

SERVICES

SOFTWARE

RESOURCES

CONTACT US

COMMUNITY

CONFERENCES

ABOUT