Store UUIDs as Binary(16)

[sfount]

For a while we have known that storing UUIDs as a 36 character (VARCHAR(36)) string is non optimal, both for the storage space it requires and for how long it takes to read/join tables on long strings.

Proposal:

• Generate UUID as 36 length string

8598abec-c985-11e5-975b-6c29955775b0

• Remove dashes to bring string length to 32

8598abecc98511e5975b6c29955775b0

• Pass this value to the UNHEX() MySQL method to store it in the database as a Binary(16)

UNHEX(8598abecc98511e5975b6c29955775b0)

• In order to read string value from the database pass the value through the HEX() method

HEX(Table.Uuid)

There are a number of research articles that seem to show storing UUIDs in this format reduces both space required and most importantly access/ join speed.

The change would mostly affect how UUIDs are written and read from the database and should not change very much application logic.
@IMA-WorldHealth/local-contributors This can be discussed and prioritised accordingly.

[jniles]

The one shortcoming of this method is we may not be able to do

var sql = 'INSERT INTO table SET ?;';

Is there any way to make this a trigger on UUID tables? That way none of the server side code has to change?

[jniles]

This proposal is crucial to having manageable database sizes. Before this can be properly considered, we will need a prototype that attempts to demonstrate INSERT syntax on a table that uses UUIDs, to understand how that will change out current API creation process.

If it is as simple as a TRIGGER, I say we go for it as soon as possible and update our APIs to use BINARY(16). If it more complex than that, we will have to have a discussion of the pros/cons in more depth.

[jniles]

Closing this as #152 has been merged.