Few questions...

[Kryptix]

I tried using PROCEDURE ANALYSE on a table of mine and saw the Optimal_fieldtype suggestion and had a few questions.

 

1. What's the difference between TINYINT, SMALLINT and INT (etc)? Some said the best option was TINYINT(3) and in another field it said the best option was SMALLINT(3), so what exactly is the difference between them? Also, what's the minimum and maximum value for each? Same with MEDIUMINT and BIGINT please.

 

2. I have a lot of fields where they will only contain either 1 or 0 so it suggested using ENUM('0','1') NOT NULL. However, phpMyAdmin doesn't seem to have a GUI for that so how would I change a column TINYINT(1) to ENUM('0','1'), and is it OK to do the same with a column that will only ever be 1, 2, 3, 4, 5 or 6 using ENUM('0','1','2','3','4','5','6')?

 

3. What's the best field setting to use for a MD5 encrypted password? I'm currently using VARCHAR(32) but it's suggesting CHAR(32). Which should I use and why?

 

4. When it says SMALLINT(5) UNSIGNED NOT NULL, what does UNSIGNED mean?

 

Thanks to whoever is patient enough to answer.

[mapleleaf]

1 and 4:

http://help.scibit.com/Mascon/masconMySQL_Field_Types.html

 

2 PhpMyadmin does have enum.

 

3 varchar is ok but I believe md5 has been broken so use sha1 with a salt

[gizmola]

1.  Mysql offers byte size integer types.  Each type matches the number of 8 bit bytes used to store them:

 

tinyint: 1 byte

smallint: 2 bytes

mediumint: 3 bytes

int: 4 bytes

bigint: 8 bytes

 

What this implies requires an understanding of basic binary math.  For example in one byte, the largest value that can be represented is 255.  This allows for values form 0-255 to be represented.

In 2 bytes that goes to 65767 and on up.  Usually you have a good idea of the min and max values you might expect in the lifetime of your system.  Being cautious and using the smallest data type that fulfills the design is a good strategy, as the size of data is the #1 performance issue related to good db design.

 

The numbers inside the quotes --- ie int(4) should be avoided.  They only come into play if you use ZEROFILL, and I have never had cause to use ZEROFILL in a db design. 

 

2. I don't see the value of using an enum.  It's a non standard data type at best, and at worst violates one of Codd's rules for relational design.  You don't save any space, as an enum still uses a byte.  Sure there's a small amount of protection in that if you only want 0 and 1, it won't let you insert a row with 3, but for true enumerated lists, you're better off having the real implied foreign key table.  Then later if you need a new value in the enumerated list, you add a row vs. alter the database structure.

 

3. An MD5 in string form is going to be 32 characters.  It will never be less, so there's no reason not to use a CHAR().  I'm not sure there's a performance advantage, although there might be.  A CHAR allocates the entire number of characters for storage even if some of the characters are not used, unlike a varchar() which only allocates what it needs.  It makes sense in this case, that you use a CHAR since you'll always use every character, and it would make sense that in this case, mysql may be able to optimize storage and retrieval knowing that the column size is fixed.

 

4. For any integer if you do not specify UNSIGNED it reserves half the available space for negative values.  So for a tinyint you can store  -127 .. 0 .. 127.  For Keys that are filled using AUTO_INCREMENT, you always want them to be UNSIGNED because the keys will be allocated as positive integers -- otherwise you risk losing half your available rows for values that will never be utilized. 

[Kryptix]

Thank you very much gizmola, quality post.