Making Your MySQL Tables More Effecient By Using Correct Fieldtypes

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

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.

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.

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.

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.

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

• MySQL Database Design - Chapter from the book MySQL: Visual QuickStart Guide
• MySQL Field Types - A great list of each field type and its technical specifications
• Official MySQL Manual - Chapter 11. Data Types

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 5 posts at DZone.