Use specific data types like SMALLINT and MEDIUMINT instead of INT and mark columns with UNSIGNED is an exaggeration?

Question

Use specific data types like SMALLINT and MEDIUMINT instead of INT and mark columns with UNSIGNED is an exaggeration?

Navigation

#1 by (2 votes)

1

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

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

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

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

mysql desempenho mariadb

asked by anonymous 05.06.2017 / 02:29

1 answer

How to print the value of a button in a text box using javascript Strange behavior in EntityFramework with StoredProcedure?

score 2 · Answer 1

Depends on context. If we are talking about a prototype or tables that will not 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 storage space and performance.

There is also the semantic question, where using a constraint such as UNSIGNED, for example, can prevent incorrect data in your database for some bug in the application.

So 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.