Basics of Database optimisation

Web developers often use the wrong data types in their MySQL databases. It is good practise to pay more attention to that part of design.
Database operations are usually the most time consuming processes during script execution.

Using the correct data types helps with database performance optimisation. The first step of database optimisation is to choose the appropriate and most efficient data type. It is common to set wrong datatypes, mostly on less loaded servers, because it doesn't drop any errors.

Internet applications using such a database are supposed to work correctly, but it cripples server performance.

When should I use UNSIGNED data type instead of SIGNED?

If you are going to use negative numbers you should use "SIGNED". With this datatype you can put numbers starting from (minus) - 2,147,483,648 to 2,147,483,647.
But if you do not need negative values, it is better to use "UNSIGNED". Then you can put values from 0 to 4,294,967,295. It is good practice to use the "UNSIGNED" datatype with ID numbers, primary keys etc. then we can have up to 4,294,967,295 rows instead of 2,147,483,647, using the same amount of memory.

Do I really need 4,294,967,295 rows in my database?

It depends how big your database would be. Most of the small and medium web sites wouldnt need such a big database. Look at the list below to choose the best data type according to your needs.

Data types

SIGNED TINYINT
Minimum Value -128
Maximum Value 127

UNSIGNED TINYINT
Minimum Value 0
Maximum Value 255

SIGNED SMALLINT
Minimum Value -32,768
Maximum Value 32,767

UNSIGNED SMALLINT
Minimum Value 0
Maximum Value 65,535

SIGNED MEDIUMINT
Minimum Value -8,388,608
Maximum Value 8,388,607

UNSIGNED MEDIUMINT
Minimum Value 0
Maximum Value 16,777,215

SIGNED INT
Minimum Value -2,147,483,648
Maximum Value 2,147,483,647

UNSIGNED INT
Minimum Value 0
Maximum Value 4,294,967,295

SIGNED BIGINT
Minimum Value -9,223,372,036,854,775,808
Maximum Value 9,223,372,036,854,775,807

UNSIGNED BIGINT
Minimum Value 0
Maximum Value 18,446,744,073,709,551,615

What is the difference between "CHAR" and "VARCHAR" datatypes?

As you probably guessed, "CHAR" data type contains a static number of characters and "VARCHAR" varies its length. This means that "CHAR" uses a static amount of memory; and "VARCHAR" can change its string length. The last byte in "VARCHAR" is used by the system.
We should use the "CHAR" type to store encoded passwords, like MD5 password hashes, or other static length data like bar codes. We should consider using "VARCHAR" when our data is varied length like e-mail addresses, names, surnames etc. Store your numeric data as a number not a string!

Database operations like search and sort are more efficient when we store our data as numbers not strings. For example, if we store IP number 192.168.1.192 as a "VARCHAR" type we should use VARCHAR(15). We can use functions like ip2long (PHP) and convert IP addresses to numbers, then our IP will be 10 characters long.

How to store Date and Time

Depending on our needs, we should choose the shortest type. If we need to store only a year we should use "YEAR" type not "DATETIME", because the second one is 8 times larger. There are 5 types of Date type in MySQL:

• DATE
• TIME
• TIMESTAMP
• DATETIME
• YEAR

"DATE" - stores date with accurancy to one day. (3 bytes)
"TIME" - stores time (hours), but not the date. (3 bytes)
"TIMESTAMP" - stores the number of seconds since 00:00:00 UTC on January 1, 1970. (4 bytes)
"DATETIME" - stores date and time. (8 bytes)
"YEAR" - stores only a year. (1 byte)