MySql – alternate of auto_increment on multiple columns

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