The 1 before the most significant digits (in 11d8) of the timestamp indicates the UUID version, for time-based UUIDs this is 1.įourth and Fifth parts would be mostly constant if it is generated from a single server. When the timestamp has the (60 bit) hexadecimal value: 1d8 eebc 58e0a7d7. Nevertheless, a collision should have a very low probability. In this case, spatial uniqueness cannot be guaranteed. A random number is substituted if the latter is not available (for example, because the host computer has no Ethernet card, or we do not know how to find the hardware address of an interface on your operating system). The fifth number is an IEEE 802 node number that provides spatial uniqueness.The fourth number preserves temporal uniqueness in case the timestamp value loses monotonicity (for example, due to daylight saving time).The first three numbers are generated from a timestamp.MySQL uses UUID version 1 which is a 128-bit number represented by a utf8 string of five hexadecimal numbers In this blog, I will explain how to store UUID in an efficient way by re-arranging timestamp part of UUID. Inserts are random and the data is scattered.ĭespite the problems with UUID, people still prefer it because it is UNIQUE across every table, can be generated anywhere.So having UUID as PRIMARY KEY makes the index bigger which cannot be fit into the memory InnoDB stores data in the PRIMARY KEY order and all the secondary keys also contain PRIMARY KEY.UUID has 36 characters which make it bulky.But here comes the problem, using it as PRIMARY KEY causes the problems described below. Many people store UUID as char (36) and use as row identity value (PRIMARY KEY) because it is unique across every table, every database and every server and allow easy merging of records from different databases. Please note, a more up-to-date follow-up post is here: Storing UUID and Generated ColumnsĪ few years ago Peter Zaitsev, in a post titled “ To UUID or not to UUID,” wrote: “ There is timestamp based part in UUID which has similar properties to auto_increment and which could be used to have values generated at the same point in time physically local in BTREE index.”įor this post, I’ve rearranged the timestamp part of UUID (Universal Unique Identifier) and did some benchmarks.
0 Comments
Leave a Reply. |