SQL Zone is brought to you in partnership with:

I am a 23-year-old living in Blacksburg, Virginia (Virginia Tech). I have been working with the web since I learned HTML in 7th grade and have been having a lot of fun with it ever since. I work for a local design and development company called New City Media as a PHP programmer and database developer. My work-load consists of mainly writing PHP code, designing database tables in MySQL 4/5 or MSSQL 2005, general tech support for our hosting, DNS, and database servers and also the occasional tech support call for a client. Mike is a DZone MVB and is not an employee of DZone and has posted 16 posts at DZone. You can read more from them at their website. View Full User Profile

Making Your MySQL Tables More Effecient By Using Correct Fieldtypes

12.02.2008
| 19652 views |
  • submit to reddit

One of the biggest mistakes of today's fledgling MySQL programmers is their field type definitions, also known as data type or column type. Many are too quick to set up their tables, flying through the different options without giving it much thought. The time you spend setting up your tables will save you from many headaches later on down the road.

What is a Field Type

The first thing to understand is what a field type really is and how it impacts your site. Selecting a field type tells MySQL how to handle and store the data inserted to that field. There are two things this affects the most: How much disk space your data takes up, and the processing power used to handle it. The more specific you define the data type the faster and more efficient MySQL will process and store your records.

Field Type Breakdown

 INTEGER

 Field TypeRange (Decimal Limit)
 INT 0 to 4,294,967,295
 TINYINT 0 to 255
 SMALLINT 0 to 65535
 MEDIUMINT 0 to 16,777,215
 BIGINT 0 to 18,446,744,073,709,551,615

Definition: An Integer is a complete entity, meaning there is no fractional value. It can be negative, positive, or zero.

Usage: Obviously only use when the data you will be entering is an integer. Normally I use INTEGER field types for for my ID field and php timestamps (seconds since unix epoch), not to be confused with MySQL Timestamps. Other times I will use this is for some sort of rating system. Like on a scale from 1 to 10.

Variety: Like most field types, MySQL has provided a variety of integer sizes to choose from. Try to anticipate what you will be storing in this column and make the appropriate choice.

 Float and Double

 Field TypeRange
 FLOAT up to 23 digits
 DOUBLE 24 to 53 digits

Definition: Floating point numbers.

Usage: These field types should be used whenever dealing with non-whole numbers. FLOAT will do just fine in most situations but if you are dealing with numbers that have 24+ digits after the decimal place than DOUBLE is what you need. This is an area where many programmers hastily choose DOUBLE over FLOAT for their field types when DOUBLE actually reserves up to almost twice the space of float.

Variety: It is important to note that because of the way DOUBLE works it should never be used for precise values such as currency.

 CHAR and VARCHAR

 Field Type Range
 CHAR 0 to 256 characters
 VARCHAR 0 to 256 characters

Definition: Small strings.

Usage: These two field types are identical in almost every way. The primary difference is the amount of space each takes up. You must specify a maximum size when you create a table, from 1 to 256 characters in length. CHAR will always take up the maximum amount of space available, filling in any extra area with space characters. VARCHAR will leave extra space along.

Variety: Each has its advantages, CHAR will take up more space but will have faster processing times, where as VARCHAR will take up less space and have longer processing times.

Text or Blob

Field Type
Range
TINYTEXT or TINYBLOB

0 to 255 characters

TEXT or BLOB

0 to 65,535 characters

MEDIUMTEXT or MEDIUMBLOB

0 to 16,777,215 characters

 LONGTEXT or LONGBLOB

0 to 4,294,967,295 characters

Definition: Large strings or blocks of text.

Usage: As you can see TEXT and BLOB have the same properties and limits. Use these field types when you have strings of data longer than 256 characters.

Variety: Most of the time TEXT or BLOB will work in most situations but if you need to store longer pieces of data such as articles or long essay's than you can use MEDIUMTEXT/MEDIUMBLOB or LONGTEXT/LONGBLOB.

Date or Time

 Field Type
Range
 DATEYYYY-MM-DD
 DATETIMEYYYY-MM-DD HH:MM:SS
 TIMEHH:MM:SS
 TIMESTAMPYYYYMMDDHHMMSS

Definition: Used to store information about a date or time.

Usage: Obviously these field types should be used whenever you need to properly store a date or time.

Variety: The TIMESTAMP field is automatically set when an INSERT or UPDATE occurs, even if no value is specified for the field. If a table has multiple TIMESTAMP columns, only the first one will be updated when an INSERT or UPDATE is performed

Choosing the Proper Field Type

There are a couple of things you should consider when designing an efficient MySQL table.

  1. The first is to identify whether your column will contain text, numbers, or a date/time type.
    • This should be simple enough but there are a few times you may want to use an integer field over a character field. For example, you may be inclined to store dollar amounts and phone numbers in a VARCHAR field because they contain hyphens and dollar signs but your database will run faster if you store them as numbers and take care of the formatting elsewhere in your script.
    • Throw out any rules you may have learned about storing numbers that you will never perform any operations on as strings. For example credit card numbers, social security numbers, etc.
  2. Next, choose the appropriate subtype to store your data.

    • Using fixed-length fields such as CHAR are more efficient, performance-wise, over variable-length fields like VARCHAR.
    • Make your fixed-length field types as small as possible. Imagine the worst-case scenario for your data, and make that the max-length. For example, if you're storing usernames and restrict the length to 15 characters make you're field limit 15 characters.

Extra Reading

Great List of References for Extra Reading

References
Published at DZone with permission of Mike Bernat, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Mc Heresy replied on Tue, 2008/12/02 - 8:31pm

From MySQL 5,the length of varchar type can more than 255 char.By the manu,its length between 0~ 65532,while the text is 65535.Becasue when you choice gt 255 char,varchar will used 2 char to store the sign.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.