2

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?

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
6
  • So you say if we have big tables with millions of rows, it's better to use simple auto increment integer value for the id column that is also the tables primary key?
    – best_of_man
    Feb 6 at 22:27
  • 1
    @best_of_man - If you need to fabricate a PK, then auto_increment is better in many ways. If you already have a unique column (or combination of columns), that is often even better. If you provide more details about the table and its usage, we might be able to advise in more detail.
    – Rick James
    Feb 6 at 23:38
  • I have a table like CREATE TABLE IF NOT EXISTS auth ( id INT AUTO_INCREMENT UNIQUE PRIMARY KEY, email VARCHAR(64) UNIQUE NOT NULL, password VARCHAR(64) NOT NULL, username VARCHAR(64) UNIQUE NOT NULL, admin INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
    – best_of_man
    Feb 7 at 0:03
  • 1
    @best_of_man - You won't be accessing that table a hundred times a second, so I am not worrying about performance. It is a toss-up of whether to have id or use username as the PK. Do other tables JOIN to this table?
    – Rick James
    Feb 7 at 1:41
  • What if this is a table for a big app like Instagram? I think with billions of user they try to reach out this table hundred of times per second. (At the moment I am not sure about the join statement. I am not that experienced professional programmer and am working on my self startup and try to learn as much as I can and do my best during my first application creation).
    – best_of_man
    Feb 7 at 3:03

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .