Optimizing columns of Mysql tables that receive only two different values

Question

I have a Mysql table with 30 columns, and 25 can receive only one of the following values: Not or Yes. Currently they are defined as char(3).

In case I convert these char columns to tinyint, receiving the respective values 0 or 1, my queries will become more optimized? Anyway, what would be the type of data most recommended for this case?

Answer 1

In case I convert these char columns to tinyint, receiving the respective values 0 or 1, my queries will become more optimized?

As darlings will not be more optimized because, in general, the type does not create optimizations, even more if the comparison is with different numerical types being used. Of course there can be a small general gain, not by query, but because occupying less space there will be more data in the cache and the load can become more efficient in some scenarios. It is not the query that gets better, and the gain depends on issues that you don’t have direct control of.

Just to be clear, the choice of the right type can influence other factors, such as the one mentioned above, and can even give the right or wrong result. It does not matter for this issue, but what influences the functioning of the database is extremely complex and not always easy to predict, so it is always right to measure and not rely on cake recipes that say what is fastest (of course measuring correctly is very difficult, who does not commit to learning everything in depth will have to be content with luck).

Anyway, what type of data would be the most recommended for this case?

It seems to me to be the same, independent of optimization, try to use the most semantically suitable, in general it will make little difference, but in fact it does not seem interesting to have 3 characters for something that can only be 2 values with a Boolean semantics.

You can try the guy BIT also, in some cases can give another gain, this seems to be a case, but to have won you need to know how to use it correctly, which is not so simple and can complicate the application for no reason if you do not need the optimization. You need to put all this data in the same column, if you use a column for each data the gain will not happen.

I could also use the type BOOL just to give you more semantics, it’s just a nickname for TINYINT.