I am writing a TypeScript-NodeJS application and want to handle object ids and created_at TIMESTAMP within the NodeJS application, instead of using MySQL or Cassandra built-in UUID or TIMESTAMP generator.
1- First of all I like to know is this a good idea to generate id and created_at values within a web server application instead of letting databases generate them?
2- Secondly, I want to know if I use database's built-in functions like uuid() or toTimestamp(now()) in Cassandra and UUID_TO_BIN(UUID()) in MySQL will this add more overhead/latency to my application compare to using NodeJS uuid() library?
Best Answer
Re MySQL or MariaDB:
Mostly the performance difference is negligible. In general, fetching a row is much more costly than any 'standard' function being used in it.
If you do need the value in both places (client and server), then it would be more convenient for you to compute it in one place.
MySQL 8.0 and MariaDB 10.7 have an optimization on UUID that is probably not available in any client -- this rearranges the bits in UUID to make them work somewhat like TIMESTAMP, thereby improving "locality of reference" for a variety of actions. Without this, UUIDs are bad for performance in huge tables. Since the optimization only works for Type 1, it is probably not available to the client.
TIMESTAMPs cannot be trusted to be unique. Hence, they should not be used [alone] in a
PRIMARY KEY.UUIDs are useful when you have multiple clients independently creating ids. Even so, usually, an auto_inc is often just as easy.