Field Type

[jaymc]

Im just wondering how much I should look into assigning field types

 

At current I only really touch INT and VARCHAR

 

INT if it only contains numbers and VARCHAR for anything else

 

for example, for an ip address, Id put that in a VARCHAR

 

Obviously that type of data may be better suited as a different more applicable type

 

But, how much does it help performance?

 

Also, can anyone provide me with a list of all the possible types along with what each type is suited to in regards to example data

 

Thanks!

[akitchin]

while performance gains are often unnoticed except in the case of huge amounts of data, using the right field type in a lot of cases greatly increases your options where queries are concerned.  a lot of comparison functions and data manipulation statements are type-dependent, such as sorting and math:

 

http://dev.mysql.com/doc/refman/4.1/en/data-types.html

 

have a look at them.  i'd advise being as precise as you can about your types, as it also discourages the intentional misuse by a malicious user, even though it's rare that a column type will be the thing to stop them.

[Wildbug]

An INT UNSIGNED is a good column to store IP addresses.  Use the INET_ATON() <--> INET_NTOA() functions to store and retreive, respectively.  You'll only use 4 bytes as opposed to 8-16 in a VARCHAR.

 

I like ENUM columns.  If you have some limited number of "conditions" in your records, like Administrator, Super User, Regular User, Limited User in a userdata row, for instance, you could use an ENUM instead of constantly repeating those strings.  One byte of storage space vs. 11-14 bytes for the strings.

 

Numeric types should have an advantage when it comes to searching just from the virtue of computer architecture.  Not to mention all the space you save when you pick the most efficient column type to match your data.  Why wouldn't you want to be efficient?