6

I wish to future-proof my application. What is the best type of identifier to use?

I am considering:

  1. 64 bit Integer
  2. UUID
  3. 64 character SHA256

I am not developing a distributed system, so is using a UUID necessary?

Also, How good are INTEGER joins vs VARCHAR joins?

Finally, is storing the username (unique) and message data in the same MySQL table better than storing the user id?

Improve this question
8

Use integers. This is the simplest and most efficient way (especially for InnoDB).

That is

  • don't use hashes or UUIDs or varchars in place of int
  • don't use the natural key when you have foreign keys to it

Reasons?

  • varchar comparisons require collation + case tests
  • index architecture in InnoDB favours integer fields (ideal clustered index is narrow, numeric, monotonically increasing)
  • hashes and GUIDs and varchar cause fragmentation
  • wider data + indexes rows when not using integer
  • 64 characters requires 64/66 bytes to store. 64 bit in requires 8 bytes

Other observations:

  • Do you need 64 bit numbers?
Improve this answer
5
  • 1
    Thanks for your input. About 64 bit numbers, May be I am being overly optimistic about what I am developing. And trying to future proof it. – user1203914 Apr 4 '12 at 11:23
  • @user1203914: hard to say. 32 bit gives you 4,000,000,000+ entries. More then enough for most databases in my experience – gbn Apr 4 '12 at 11:54
  • I agree it gives around 4,294,967,296. Thinking of using the first 32 bit for may be dB id etc in the future. – user1203914 Apr 4 '12 at 14:16
  • @user1203914: My experience also says "code what you know now". Not in the future. Also, do you want to contrive an artificial key when you should use 2 separate fields? – gbn Apr 4 '12 at 14:24
  • You have a point. I will be sticking with 32 bit integers for now. :) Thanks again. – user1203914 Apr 4 '12 at 14:37
3

UUIDs/GUIDs are desirable when you want multiple, independent, computers creating Globally Unique IDs.

But they suck as a PRIMARY KEY.

They are big -- 36 characters, worse if you default to utf8, which is overkill. The hex in them can be converted to BINARY(16). In InnoDB, the PK is appended to every secondary key, thereby multiplying the space cost of a bulky PK.

They are random -- Once you have more data than you can cache, you are usually hitting the disk for every fetch. This drops your performance to ~100 operations/sec. Just when you need scalability, it is taken away from you!

If all writes are done to a single Master, then use AUTO_INCREMENT: MEDIUMINT UNSIGNED up to 16M, INT UNSIGNED up to 4G, or BIGINT for the insanely optimistic.

As for the CPU performance of VARCHAR, BINARY, INT, etc -- that's not the issue. The issue is the bulkiness of the key because that leads to the cacheability of the INDEX, which leads to I/O being the bottleneck. If everything can be cached, don't worry about the key type and size. (OK, the datatypes in a JOIN must be the same, or close enough. Different collation, for example, destroys the usability of an index.)

Improve this answer
1
  • even in a multimaster setup you can still use auto_increment with auto_increment_increment and auto_increment_offset set appropriately. Though you'll want to be more liberal in deciding your maxint limits – atxdba Sep 11 '12 at 4:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.