Using Redis for MySQL autoincrement

Sometimes when we use sharding in MySQL or other SQL database, we can not rely on MySQL autoincrement, simply because we have several tables where we insert the data.

In Postgres and Oracle there are special objects called "sequences" that may give you unique id which you can use in such case. However MySQL have no sequence objects.

One of the ways is to simulate a sequence with table like this:

	id int      unsigned not null auto_increment primary key,
	tag tinyint unsigned

Then we need to insert a record there, and to get last-insert-id, which to be used in real table.

This works well, but sometimes on high traffic websites, MySQL autoincrement slows down entire database. Slow down may be 1000% or even more. In that case we can do the job with Redis, using following script.

The script uses INCR command. The script also checks if autoincrement key is not existent. In such case data is got from MySQL.

Because many clients may request same information from MySQL, the script employ a lock.

function get_next_autoincrement_waitlock($timeout = 60){
	$count = $timeout > 0 ? $timeout : 60;

		if ($count > 10)
			return false;

	return true;

function get_next_autoincrement($timeout = 60){
	// first check if we are locked...
	if (get_next_autoincrement_waitlock($timeout) == false)
		return 0;

	$id = $r->incr("serial");

	if ( $id > 1 )
		return $id;

	// if ID == 1, we assume we do not have "serial" key...

	// first we need to get lock.
	if ($r->setnx("serial:lock"), 1){
		$r->expire("serial:lock", 60 * 5);

		// get max(id) from database.
		$id = select_db_query("select max(id) from user_posts");
		// or alternatively:
		// select id from user_posts order by id desc limit 1

		// increase it

		// update Redis key
		$r->set("serial", $id);

		// release the lock

		return $id;

	// can not get lock.
	return 0;

$r = new Redis();
$r->connect("", "6379");

$id = get_next_autoincrement();

if ($id){
	$sql = "
		insert into user_posts(
			id		,
			user		,
			$id		,
			'$user'		,

	$data = exec_db_query($sql);

Redis connection note

The example uses generic connection to the Redis server. To make the example work with our service you will need to use code similar to this one:

// change these parameter according to the information in your instance list
$host = '';
$port = 1234;
$password = 'somehashcode';
$db = 0;

$r = new Redis();
$r->connect($host, $port);

blog comments powered by Disqus

Code library

Because we want this page to be useful for memcached users, we tagged with memcached all examples that may be "recreated" for memcached server.

MySQL cache in PHP

How to use Redis for cache MySQL queries in a way similar to memcached
Date: 2011-07

Tags: cache memcached mysql php

How to prevent website to be web-scraped

How to use Redis to prevent web-scraping parsing and web-spam
Date: 2011-07

Tags: memcached webstats security php anti-spam

'Rotating' news

How to do 'Rotating' news list
Date: 2011-07

Tags: php

Show random element

How pool random element from news list
Date: 2011-07

Tags: php

Accounting / Vote example

How Redis can help us with Accounting / Vote / Like / Recommend / +1 clicks
Date: 2011-07

Tags: mysql php

Scoreboard example

How to implement fast hi-score table
Date: 2011-07

Tags: php

Scaling example

How to scale Redis on several servers using sharding
Date: 2011-07

Tags: cloud memcached sharding scaling php

Distributed work pool / pipeline example

How to implement distributed work pool and to scale some work across many computers
Date: 2011-07

Tags: scaling php cloud

Simple realtime web counter

How to collect and store information for page visitors
Date: 2011-07

Tags: webstats php

Emulation of expiration of the set members

How to make set members to expire
Date: 2011-07

Tags: php

Lock example

How Redis can help us with user level locks
Date: 2011-08-23

Tags: memcached lock php

Message queue example

Creating distributed message queue
Date: 2011-09-17

Tags: scaling queue php cloud

Index example

Building reverse index using sets
Date: 2011-11-01

Tags: php

Using Redis for MySQL autoincrement

Using Redis to speed up MySQL inserts
Date: 2011-01-13

Tags: mysql scaling php cloud

Using Redis as "data buffer"

Using Redis for caching MySQL or Cassandra data inserts
Date: 2012-08-04

Tags: cloud mysql sharding webstats scaling php cassandra

Calculating how many visitors are on the same webpage using rolling average

How to produce 'There are 123 visitors on this page' sign
Date: 2011-07

Tags: memcached webstats php

© Jul.2011 - 2019,
This website uses cookies to improve user experience. By using our website you consent to all cookies in accordance with our Cookie Policy.