Is using specific data types like SMALLINT and MEDIUMINT instead of INT and marking columns with UNSIGNED an exaggeration?

Question

Speaking of databases, we know that they have several data types to be used in each specific situation. But, following the data types to the letter can be considered an exaggeration in most cases or is always recommended, even giving more work to the developer?

And taking advantage of the question, about UNSIGNED (which for those who do not know, serves to inform the bank that that column will not accept negative values. This also increases the positive column limit value), it is also an exaggeration to uselo in all columns that will not receive negative values (which in most cases will be the majority)? Using UNSGINED, in addition to increasing the limit, ensures better performance or not? Since the amount of possible values remains the same, I believe that.

I have little experience with databases. Unfortunately it is a resource that although we always use, we end up leaving aside and doing what everyone does.

If you have anything else that fits the question and you want to add, feel free.

Answer 1

Depends on the context. If we are talking about a prototype or tables that will be very large, it can be an exaggeration to spend a lot of time optimizing datatypes. However, if they are tables that will reach millions or billions of lines, each byte saved in a row will represent several Gigabytes in your dataset. This will certainly impact on storage space and performance.

There is also the semantic issue, where using a constraint like UNSIGNED, for example, can avoid incorrect data in your database by some bug in the application.

Therefore, whenever possible understand the nature of the data and use correct datatypes, but be consistent with your particular case, as it is not uncommon to use "wrong" datatypes in smaller prototypes or applications.