Professional and Affordable Web Design

0800 080 5401

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)

Comments2 Comments

Web designer

Database efficiency is often overlooked by programmers as well as web designers. It is quite common not to optimise the database. Improving the efficiency of database can have a big impact on its long term stability. Having the right structure and data type is the building block for an efficient database and is a must for any professional web application.

19 June 2009

MCB Web Design

I've always wondered about the difference between char and varchar. Thanks for clearing that one up! And an insight into the difference between signed and unsigned numbers was good to know too.

18 May 2010

All comments are moderated for spam and will not be shown. All genuine comments wil be show, however the links will be based on a NO FOLLOW RULE. Repeat commenters adding value to the articles and discusions will have removed alowing Follow Rule to Work.

Make a Comment

Notify me when someone responds

Quick Contact

See Full Portfolio Some Of Our Work

Screenshot of dsbs.co.uk website
dsbs.co.uk

Driving Schools Booking Service (DSBS) is a network of driving instructors, covering the whole of the UK. For this project, we were...

Screenshot of countysecurity.co.uk website
countysecurity.co.uk

County Security is a fully featured E - commerce solution, with an integrated "system configurator", which allows users to choose...

Customer News & Resources

At Mutiny Design we are constantly gathering together articles and help guides to assist our clients.

Introduction to sitemap.xml

Checking for a sitemap A site map (or sitemap) is a list of pages of a web site accessible to crawlers or users. It can be either a document in any form used as a planning tool for web design, or a web page that lists the pages on a web site, Some developers feel that site index is a more appropriately used term to relay page function, web visitors are used to seeing each term and generally associate both as one and the same. However, a site index is often an A-Z index that provides access to content, while a site map provides a general... Read More »

Center a web page in CSS

One simple way to center a web page using CSS is to create a container div, that is horizontally centered by having its left and right margins set to auto. Using this method, you can still apply colours / background images to the body tag, so its a flexible solution. (if you didnt need this ability, just apply margins and width to the body tag instead, and forget using the container div). The container div has the same width as your webpage and, well, contains it. All the code for your web page is placed inside the container div. This will... Read More »

-