In the case of a 32-bit hexadecimal value (UUID), what’s the advantage of binary(16) versus char(32)? If you have to use unhex() and hex() calls everytime you read or write the values, would that overhead make char(32) perform better?

Btw, thanks for this great post!
danilo

Thanks for sharing your experience.
In two customer cases I’ve had, and in which we’ve changed data types throughout the schema, we’ve reached 50% savings in disk space, just due to proper data types + charsets.
I personally see this as basic and important part for auditing.

Obviously, there are other parameters to consider. But I think laying out the basics properly, gives boost to further steps.

Regards.
Shlomi

In my experience having smallint vs int was more to save space but does not necessarily influence performance at least not something noticeable. Many times I come across some white papers where people are trying to prove a point, I run the test and the method they are trying to prove wrong has better performance than the one they are in favor. I have done such test even with the white paper coming out of the company that created the software.

Most of the time, the approach to design / architect databases or application depends on what you are trying to accomplish and what kind of usage the system will be for. Doing consulting for many many years, I’ve come across multiple approaches and styles and if sit down and analyze each one of them they all would have valid justification for their approach or design. It is funny but the more experienced I’ve gotten the less adamant and less critical I have become. This does not mean that I am in favor of carelessness and the obvious.

Thanks,

Ann

Not really 🙂
I stand corrected on hexadecimal values;
IPv4 is still *very much* widespread;
MEDIUMINT is undesired on InnoDB.

The arguments are still very much valid. Not as starting point, but as start-to-end argument.

(Though I am very much susceptible to error and will gladly stand corrected on any point I make)

Regards

Ann

Maybe.