Hi,
In MyISAM you can set 2 columns to have auto-increment properties.
So, in a messaging system you could have:
user_id, message_id
INSERT INTO messages (user_id, message) VALUES (1, ‘My Message’);
INSERT INTO messages (user_id, message) VALUES (1, ‘My Message’);
INSERT INTO messages (user_id, message) VALUES (1, ‘My Message’);
A SELECT * would produce
user_id, message_id
1,1
1,2
1,3
InnoDB does not currently support this, so I’ve done the following work around:
INSERT INTO messages (user_id, message_id, message) VALUES (1, MAX(message_id), ‘My Message’) WHERE user_id=1;
Are there any known problems with self-implementing support this way?
It appears to be working fine in the development enviroment, however with thousands of users… might be a different story!
Cheers