MySql - alternate of auto_increment on multiple columns

Question

Since auto_increment on multiple columns is supported in MyISAM engine only and I can't use it because of its drawbacks (table lock, slower writes, no-transacts etc), I am looking for an alternative.

One thing I am already doing is to get the last id like following

Table

KEY1 INT NOT NULL -- PART OF PK
KEY2_ID INT NOT NULL -- PART OF PK
OTHERCOLUMNS ...


SELECT KEY1,IFNULL(MAX(KEY2_ID),0) + 1 NEXT_ID FROM TABLE1 WHERE KEY1 = ? GROUP BY KEY1;

Sample data

KEY1     KEY2_ID   OTHERCOLUMNS
1        1
1        2
2        1
2        2
2        3

When the above query is run for the key1=1 NEXT_ID will be 3 and for key1=2 it will be 4

Is there any other way we can do it?

Update: In response to DTest & Chris Travers

I am designing db for my browser based online game where game state data (users game objects like inventory including potions, weapons, buildings, livestock etc) is stored in multiple user_xxx tables.

For example, I have a table user_map where all user buildings (including road path), decorations (chair, lamp posts etc) is stored.

map_items
---------
id(pk)       name     type       dimensions (this is actually stored in two separate columns)
1        "bakery"  "building" "3x3"
2        "chair"  "chair"    "1x1"
3        "main road"  "road"      "2x2"

Above table is the lookup table which a player can buy and place in his virtual world

user_map
-----------
user_id(pk)       map_item_id(pk)       instance_id(pk)      location     state
1234              1                     1                    "10,10"      "producing"
1234              3                     1                    "15,15"      "na"
1234              3                     2                    "17,15"      "na"

Since a user can build/place same object multiple times, I am maintaining the instance_id so that user_id + map_item_id + instance_id will give a unique item

Like above table, I am maintaining instance_id for remaining tables like user_weapons, user_mounts, user_inventory etc.,

Now, to answer why I don't want to user a surrogate key is that I am afraid of limitation of an auto_increment column which will some day going to exceed the maximum number of the data type supports. I am actually infavour of using surrogate key (I actually started changing the model before starting this post and stopped)

Probably I am worrying too much about it because the length mysql supports looks fine but some how the using the unsigned ubigint might slow down my queries?

• unsigned int 4,294,967,295
• unsigned bigint 18,446,744,073,709,551,615

Answer 1

You could use a trigger. The key problem there though is that it will have to do a lookup and so multiple concurrent inserts into the table may get messy (concurrent being within the life of the transaction).

A real alternative here is probably going to go with a natural candidate key (multiple columns of actual rather than artificial data) and add a single autoincrement key for easy joins. Having a single column reference makes joins to the table a lot easier.

Those probably aren't the answers you want to hear but they should work. If they won't work, then the question is why and what data modelling scenario you are trying to address.