MySQL Data Types: general question / answer

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.

3 thoughts on “MySQL Data Types: general question / answer

  1. Stefan says:

    Don’t you mean the VARCHAR(1) column should be replaced with a CHAR(1) instead of TINYTEXT ?

    @stefan: agreed, I would personally use CHAR but I’m trying to explain why in this case proc analyse() would suggest TINYTEXT instead of VARCHAR

  2. Salman says:

    mysql data type tinyint(2) here what means by 2 the range of tinyint is -128 to 127 or 0 to 255 but the fact is in tinyint(2) when we create table here 2 means what .. when i write tinyint(2) it also accept number with in the tinyint range and if i write like tinyint(1) or tinyint(4) it that also accept the range then here tinyint(2) what the 2 means..

  3. [...] type to use for a single character – Matt Reid talks about VARCHAR, TINYTEXT and TINYINT in MySQL Data Type Q&A. In the Oracle world, Randolf Geist has a step-by-step HOWTO for Basic SQL Statement Performance [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>