Comments on: On generating unique IDs using LAST_INSERT_ID() and other tools https://shlomi-noach.github.io/blog/mysql/on-generating-unique-ids-using-last_insert_id-and-other-tools Blog by Shlomi Noach Fri, 22 Feb 2013 09:37:33 +0000 hourly 1 https://wordpress.org/?v=5.3.3 By: Nicholas Shanks https://shlomi-noach.github.io/blog/mysql/on-generating-unique-ids-using-last_insert_id-and-other-tools/comment-page-1#comment-183679 Fri, 22 Feb 2013 09:37:33 +0000 https://shlomi-noach.github.io/blog/?p=3283#comment-183679 I just use femtosecs_since_big_bang(), which fits quite nicely into a 128 bit integer with six bits to spare.

]]> By: Teiksma https://shlomi-noach.github.io/blog/mysql/on-generating-unique-ids-using-last_insert_id-and-other-tools/comment-page-1#comment-40643 Mon, 23 May 2011 09:04:05 +0000 https://shlomi-noach.github.io/blog/?p=3283#comment-40643 I use following construct:

1. table mysysid(id int);

2. function newsysid with following lines:
update mysysid set id = id + 1 where @newid := id + 1;
return @newid;

3. then simply use select newsysid();

Modifying given function with +parameter instead of + 1 one can get more than one new ids with function returning first of them

]]> By: Peter Laursen https://shlomi-noach.github.io/blog/mysql/on-generating-unique-ids-using-last_insert_id-and-other-tools/comment-page-1#comment-29579 Wed, 02 Feb 2011 12:22:53 +0000 https://shlomi-noach.github.io/blog/?p=3283#comment-29579 Sorry Shlomo, but I have no such benchmarks.

My concern is only that after updating using a calculation on LAST_INSERT_ID, one of two (as far as I can see) valid INSERT syntax’es fail (ie: if NULL is not specified explicitly).

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/on-generating-unique-ids-using-last_insert_id-and-other-tools/comment-page-1#comment-29578 Wed, 02 Feb 2011 12:00:20 +0000 https://shlomi-noach.github.io/blog/?p=3283#comment-29578 Hi Peter,

Funny indeed. Karma, I would say!
Do you find the way you used sequence generation (with AUTO_INCREMENT) to work faster / with less locks than the original documentation suggests?

]]> By: Peter Laursen https://shlomi-noach.github.io/blog/mysql/on-generating-unique-ids-using-last_insert_id-and-other-tools/comment-page-1#comment-29573 Wed, 02 Feb 2011 11:52:45 +0000 https://shlomi-noach.github.io/blog/?p=3283#comment-29573 It is funny actually that you posted this at the same time as I posted http://bugs.mysql.com/bug.php?id=59885

]]> By: shlomi https://shlomi-noach.github.io/blog/mysql/on-generating-unique-ids-using-last_insert_id-and-other-tools/comment-page-1#comment-29550 Wed, 02 Feb 2011 08:13:30 +0000 https://shlomi-noach.github.io/blog/?p=3283#comment-29550 @Rami,
doing something like LAST_INSERT_ID(id+1000) sounds just fine to me. Then you can get the result number and the 999 below it and use by the webapp.
I would suggest that the likelihood of all webapps returning to get the next 1000 values – all at the same time – recurringly – is zero. That is, it will eventually evenly spread. Isn’t the anthropic law in place here 😉 ?

]]> By: Rami Citrom https://shlomi-noach.github.io/blog/mysql/on-generating-unique-ids-using-last_insert_id-and-other-tools/comment-page-1#comment-29544 Wed, 02 Feb 2011 07:19:19 +0000 https://shlomi-noach.github.io/blog/?p=3283#comment-29544 How about caching the sequence in the application itself?
Get the application to generate N session id’s at a time, allocating as needed:
UPDATE sequence SET id=LAST_INSERT_ID(id+N);

When the application used all N session id’s it will request more from the DB.

This should reduce the load on the sequence table by a factor of N.

If each application uses a random multiple of N, it should also prevent all applications trying to refresh their cache at the same time – which could happen in an evenly distributed load . . .

]]>