Oracle Performance – Sequences vs Count for Auto-Increment

UUIDs are a performance disaster for very large tables. (200K rows is not "very large".)

Your #3 is really bad when the CHARCTER SET is utf8 -- CHAR(36) occupies 108 bytes! Update: There are ROW_FORMATs for which this will stay 36.

UUIDs (GUIDs) are very "random". Using them as either a UNIQUE or a PRIMARY key on large tables is very inefficient. This is because of having to jump around the table/index each time you INSERT a new UUID or SELECT by UUID. When the table/index is too large to fit in cache (see innodb_buffer_pool_size, which must be smaller than RAM, typically 70%), the 'next' UUID may not be cached, hence a slow disk hit. When the table/index is 20 times as big as the cache, only 1/20th (5%) of hits are cached -- you are I/O-bound. Generalization: The inefficiency applies to any "random" access -- UUID / MD5 / RAND() / etc

So, don't use UUIDs unless either

• you have "small" tables, or
• you really need them because of generating unique ids from different places (and have not figured out another way to do it).

More on UUIDs: http://mysql.rjweb.org/doc.php/uuid (It includes functions for converting between standard 36-char UUIDs and BINARY(16).) Update: MySQL 8.0 has a builtin function for such.

Having both a UNIQUE AUTO_INCREMENT and a UNIQUE UUID in the same table is a waste.

• When an INSERT occurs, all unique/primary keys must be checked for duplicates.
• Either unique key is sufficient for InnoDB's requirement of having a PRIMARY KEY.
• BINARY(16) (16 bytes) is somewhat bulky (an argument against making it the PK), but not that bad.
• The bulkiness matters when you have secondary keys. InnoDB silently tacks the PK onto the end of each secondary key. The main lesson here is to minimize the number of secondary keys, especially for very large tables. Elaboration: For one secondary key, the bulkiness debate usually ends in a draw. For 2 or more secondary keys, a fatter PK usually leads to a bigger disk footprint for the table including its indexes.

For comparision: INT UNSIGNED is 4 bytes with range of 0..4 billion. BIGINT is 8 bytes.

Italics Updates/etc were added Sep, 2017; nothing critical changed.

Addressing Ivan's answer and the link he provided.

• Yes, there are benefits of a distributed id-generator. But...
• There are other ways to avoid the delay of a centralized ID server -- Dispense 100 consecutive ids at a time to the client. Or use a client-generated concatenation of client_id and time.
• If the entire index is cached in RAM, then much of my argument is moot. But once it spills to disk, performance suffers severely. His article does briefly point out that UUIDv4 is slower than UUIDv1, but fails to point out that the bits need rearranging to achieve the benefit.
• I agree with squeezing a 36-byte UUID into a 16-byte BINARY. MySQL 8 even has a function to do such. Furthermore, it (and my blog) rearrange the bits so that UUIDv1 has the temporal characteristic of an auto_increment.
• Sharding, I contend, is not normally done against auto_increment ids, so switching to UUIDs does not buy anything.

All the comments above are appropriate.

If you provide the details on the data types and any not null constraints of the columns in question, then we can better determine whether your function-based approach is appropriate. I would also gather statistics on the indexes and look at the unique values, etc. You can compress your baseline index too, if say you have very few unique values for company and hire date, you can compress by two levels. In fact, you can do the following to see if index compression will help:

analyze index IEMP_CMPHIRDEP validate structure;

Then query the view index_stats:

SYS@instance> select OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE from index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             3               28

In the above example, compressing the index at level 3 would save 28% space. So if it is a good amount, you would

alter index IEMP_CMPHIRDEP rebuild compress 3;