4 ms·
> Isn't creating a sequence a bad idea in general, anyway? No: - sequences are very common. I recommend using them on every table for mgmt. and internal effic
by redis_mlc 5y ago
> Isn't creating a sequence a bad idea in general, anyway?
No:
- sequences are very common. I recommend using them on every table for mgmt. and internal efficiency reasons.
For example, with Innodb, if you don't have a numeric id as a PK, it will assign an invisible one for internal use anyway.
Most third-party tools won't allow you to manage tables without numeric PK's.
- in most large applications, most sequence ID's are only used internally
- for public display (your concern) uuids or random numbers are possible
Source: DBA.
- evanelias 5y agoI agree with your overall point, but wanted to clarify one topic. > with Innodb, if you don't have a numeric id as a PK, it will assign an invisible one for internal use anyway. There's no requirement that your PK be numeric with InnoDB. A monotonically increasing numeric ID will have the best performance, yes. But even a small-ish varchar PK may perform better than relying on InnoDB's internal invisible one, depending on the workload. InnoDB will only use an invisible numeric PK if you have no explicit PK defined, and you either have no UNIQUE KEYs at all either, or all of your UNIQUE KEYs have nullable columns. The column type of your PK is irrelevant though. The invisible numeric PK is terrible because it uses a system-wide lock (or at least it did prior to 8.0, not sure if this has been fixed). So if you're inserting at any real volume to multiple tables like this, performance suffers badly. Worse still, the lock it uses is the dict_sys mutex, which other code paths (e.g. DROP TABLE) also hit.