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