mysql uuid vs auto increment performance

> This is because the inserts are going to happen all over the place, and the way tables are stored definitely prefers appends. This matters if you have gazillion-row DBs, surely? I dealt with a near-billion row DB and shaving a few bytes off a row would matter to us bigly.įor the sake of argument I'll assert that you should never use UUIDs as keys, but please feel free point out where I'm wrong. Only exceptions I've encountered to my 'natural key' claim are addresses, which are a sod to canonicalise (in fact, seemingly impossible, I'd welcome any pointers).Īlso UUIDs are bloody big keys, so fattening up tables and impairing disk/memory bandwidth. you could have the same row 10000000 times but falsely uniquified with a different GUID. Why add a fake key? If it's to enforce uniqueness then that uniquness is spurious because it's a GUID which has no meaning to the data to which it's attached eg. > Often UUIDs are used as keys to things. I've found myself adding AND id BETWEEN to queries, and using them as a partition key, and using them to DELETE old data etc, and various other admin stuff that the original creators of the table never thought about.

Once you have ULIDs and you know you have UILDs, a lot of database type tasks become easier because the ID encodes a creation date. So even though I didn't see it discussed and called ULID until fairly recently, its a very old and well-known trick in some circles.

I've build gazillion-row databases where all the data sources have, independently, used this same high-bits-are-timestamp trick. is fairly recent, but the 'trick' has been used for years. This turns inserts into, approximately, appends.

So a common word of wisdom is to have an auto-increment primary key, and the uuid indexed! Ugh what a work around.Ī better way is to discover ULIDs, which are like UUIDs but with the high-bits including a timestamp. This is because the inserts are going to happen all over the place, and the way tables are stored definitely prefers appends. The moment you have any kind of load on such a table, your performance goes to hell. In terms of possible collisions, for example using v4 UUIDS (random), the probability to find a duplicate within 103 trillion version-4 UUIDs is one in a billion.Often UUIDs are used as keys to things.Īnd these things are often stored in databases.Īnd usually the database puts them into a btree internally, because that's how tables are stored. Plus you can easily generalise/update your database schema and/or clustering scheme without having to worry about IDs breaking and causing conflicts. For example one can generate UUIDs without using/accessing the database at all, or even use UUIDs which have been pre-computed and/or stored somewhere else. That said, in many cases UUIDs would be indeed preferable. I would read carefully the mentioned references and decide whether to use UUID or not depending on my use case. The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of.Cumbersome to debug ( where userid='').It is a whopping 4 times larger than the traditional 4-byte index value this can have serious performance and storage implications if.Most replication scenarios require GUID columns anyway.You can generate IDs anywhere, instead of having to roundtrip to the database.Allows easy distribution of databases across multiple servers.Allows easy merging of records from different databases.Unique across every table, every database, every server.UUID pros / cons (adapted from Primary Keys: IDs versus GUIDs) ( UUID vs auto-increment performance results, adapted from Myths, GUID vs Autoincrement) Primary Keys: IDs versus GUIDs (coding horror).Performance: UUID vs auto-increment in cakephp-mysql.Having a column as Primary Key should make both choices equal wrt to performance. Of course UUID may take a little more time (and space), but this is not a performance bottleneck for most (if not all) cases. Most posts (including some by the authors of this site), state as such. In terms of performance, there shouldn't be any noticeable difference using auto-increment over UUID. AUTO_INCREMENT) will return a unique integer for the specific DB and DB table, but which is not universally unique (so if imported to another DB chances are there will be primary key conflicts). On the other hand a simply INT primary id key (e.g. Values, even if these calls are performed on two separate computers Two calls to UUID() are expected to generate two different UUID returns a universal unique identifier (hopefuly also unique if imported to another DB as well).Ī UUID is designed as a number that is globally unique in space and

Mysql uuid vs auto increment performance