TheMattReid

Question: “When I use procedure analyse() on my schema it suggests TINYINT for the columns which have the data type VARCHAR. Based on the performance and data requirements, which one is better?”

Answer: TINYTEXT and TINYINT and VARCHAR are quite different. For reference I would refer you to the mysql manual page about data types.

However, procedure analyse() will read the values you have in your columns and if they consistently fit a pattern that would be better suited to another data type then it will suggest the correct one. As in, if your column is VARCHAR(1) and your data is similar to “1,4,7,5,2″ etc then TINYINT would be a better suited data type since you are dealing with numbers and not variable characters. Similarly, if you have the same varchar column, but your data is “a,b,t,h,o” etc then TINYTEXT or CHAR would be better than VARCHAR for this type of data. Mostly, CHAR would be better for my example here but your datasets might make mysql think TINYTEXT is better.

In regard to data storage, which procedure analyse() also looks at given the data you have, here are the following differences between the three data types.

TINYINT: 1 byte
TINYTEXT: 1 byte length prefix plus data
VARCHAR: 1 or 2 byte prefix plus data depending on string length.

So you have to choose the data type depending on the data it’s going to store. There’s no reason to store 2 byte prefixes and then the data if you can just store the 1 byte of data. In addition, the various TEXT data types have other attributes that are bigger conversation, see the documentation for more details there.

That said, if you are running procedure analyse() on your data and it recommends TINYTEXT when you have simple testing data like I mentioned above, but you choose VARCHAR because you know that your data will eventually *need* that type of storage requirement – say it will eventually store multi-byte usernames or passwords in excess of 255 characters (since VARCHAR can hold more data than TINYTEXT), then you should not change your column to TINYTEXT. This is because procedure analyse() is only recommending this because of the current data, not future data – so you need to involve your own interpretation for the optimization process.