Catching up with some very old emails...
>>>>> "Tim" == Tim Bunce <Tim.Bunce@stripped> writes:
Tim> This thread reminds me of a separate but related issue.
Tim> It can often be useful to have a way to generate globally unique
Tim> values that are guaranteed to not clash with any other value anywhere
Tim> at anytime (even when merging data from systems that were previously
Tim> not connected).
Here is a description of a function that we plan to add to 4.1:
Implement a function OID() that should return a 64 bit unique number for each invocation.
To make this independent of the server, it should construct the number as follows:
server_id << 45 +
(unix_timestamp_for_mysqld_start & (1 << 32-1)) << 24 +
(Here count is a 64 bit number)
The function has the following restrictions:
- We assume that server id < 512 (or at least the lowest 9 bits are unique)
- The high bit for timestamps are not relevant (shouldn't be safe)
- We don't execute more than 15 million OID() during one second
(To be exact, during X seconds of server uptime, we are not executing
more then X * 15 milllion OID() queries)
- That the timestamp is different if the server restarts
One major benefit of the above is that it 'only' takes up 64 bits
Tim> p.s. Another plus point for GUIDs is that the binary storage can be
Tim> arranged to not be sequential and thereby avoid frequent re-balancing
Tim> of b-tree indices on inserts. (Though that could also be avoided for
Tim> indices on other types like integer auto_increment and date fields
Tim> if MySQL supported the ability to index a value in reverse:
I thought about the above but I don't really know if the argument is
- When updating the leftmost node in a b-tree, MySQL doesn't do any
index balancing but splits the page before the last key.
The new leftmost key page will thus only contain 2 keys and the
other key page will be almost full.
The nice thing with this algorism is that if you write sequential
numbers to the b-tree, you will get better:
- Most keys are written to the end of the key block, which is a fast
operation as you don't have to move much data.
- Better key leaf utilisation as most key pages will be full
- More hits in the key cache as you will many times access the same
- Much fewer updated blocks in the key cache to be flushed out later