Performance of using UUID as a primary key

[VictorLion]

I am building a customer database. The database will have a primary key. I am looking to make the primary key either a unique integer or a UUID (with 32 chars). The UUID appeals to me due to its ability to sync tables. The primary key will be used for (obviously):

1. Searching and sorting records
2. As a tag against documents eg document XX is linked to customer AA, BB and CC; and 
3. A foreign key to link tables

My questions are:

1. Do UUIDs adversely affect database size
2. Do UUIDs adversely affect speed

If UUIDs retain their full 32 chars, then there will obviously be a disadvantage.

However, is FM clever enough to convert the UUID "under the hood" (such as to an integer referenced index) to improve database speed and/or size.

[Wim Decorte]

5 hours ago, VictorLion said:

 

1. Do UUIDs adversely affect database size

 

Yes.  But does it matter?  How many records do you expect?

 

5 hours ago, VictorLion said:

 

1. Do UUIDs adversely affect speed

 

No.

 

[Mike Duncan]

Indexing a primary key stored as text may add some overhead, but not generally too large of a concern unless your tables are expected to get very large. The built in function for UUID will need to be stored as text for relationships to work. If you still want to use integers for UUIDs there are other algorithms to produce those. I would consider Jeremy Bante's custom function which you can find here:

https://github.com/filemakerstandards/fmpstandards/blob/master/Functions/UUID/UUIDTimeDevice.fmfn

[jbante]

Using text UUIDs can make certain database operations very slightly slower than their numeric equivalents, but I've never seen an application where that was the performance bottleneck. There's practically always something else you can do to get a more substantial speed improvement.

If you do insist on numeric UUIDs, my function that Mike linked to could work. I started that family of functions before FileMaker introduced the Get ( UUID ) functions. Now that we have Get ( UUID ), you can just use one of many functions to convert Get ( UUID ) from hexadecimal to decimal. This is slower to generate each UUID, but the resulting UUIDs are slightly smaller and there's better security since the content is driven by the random number generator behind Get ( UUID ). (The result of the Get ( UUID ) function is a type 4 UUID, which is supposed to be created by a cryptographic-strength random number generator.)

[comment]

9 hours ago, VictorLion said:

The primary key will be used for (obviously):

1. Searching and sorting records

UUIDs are random values; using them for sorting is meaningless.

 

9 hours ago, VictorLion said:

Do UUIDs adversely affect database size

To some extent. You can minimize this extent significantly (IMHO) by allowing only minimal indexing of the UUID field.

 

9 hours ago, VictorLion said:

Do UUIDs adversely affect speed

Not as such, but ... If you also  elect to validate the field as unique, that might slow down some operations, e.g. creating new records, as the number of records increases. There's a dilemma here: the probability of generating a duplicate is extremely small - but it's not entirely zero.

 

9 hours ago, VictorLion said:

However, is FM clever enough to convert the UUID "under the hood" (such as to an integer referenced index) to improve database speed and/or size.

I don't know that there would be a perceptible improvement in either speed or size. "Under the hood" everything is kept as binary numbers.

 

Edited August 8, 2016 by comment