Optimize Your MySQL Database

MySQL is a pretty fast database and serves most requirements for a decent
sized Web application. But the dragon starts raising its head, when the number
of records grow. In this series we will look into various ways of optimizing the
MySQL database so as to tame the dragon from time to time.

The simplest optimization in MySQL is to choose correct data types, depending
on the record set stored in the table. Many MySQL database designers fail to do
this, resulting in database driven websites failing miserably. In this article,
we will cover the most common data types used in MySQL and how to use built-in
MySQL functionalities to figure out the most optimal data type for a column.

Size does matter

A standard practice among database designers is to define primary key as an
auto-increment INTEGER. By default MySQL will set this as Unsigned INT. This
means you can store 4,294,967,295 records in the given table. Sounds good from a
scalability point of view but if you are designing the table for a small website
ask yourself a question, is your website going to get 4,294,967,295 users ? Of
course we wish your website gets these many users but most websites do not have
so many users.

Thus choosing Unsigned INT as userID might not be a good choice. For small to
medium sized websites choosing MEDIUIMINT as userID will be the bast choice.

When compared to INT, MEDIUMINT consumes approximately 25% less space. A
column with INT type consumes 4 bytes whereas a column with MEDIUMINT type will
consume 3 bytes.

MySQL has several specialized data types which require lesser memory and
space. Smaller the data type size, faster is the record look up and faster the
query execution. Specially the primary index of a table should be as small as
possible to make row identification faster.

Of course when more users start joining your website you can always alter the
table to go to next higher data type. MySQL manual is a good source to know size
of different data types and identify the most suitable, smallest data type for a
column. Storage requirements for each data type can be found here http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html
.

Rule to remember is to start small and grow the field type as record set
increase in the table.

NULL is not NULL

NULL columns require additional one bit per column. Setting column as NOT NULL
can make everything faster. Specially when your query involves subqueries, MySQL
will perform an additional search for rows that contain NULL. This search can be
avoided when there are no NULLs in the table. You should definitely use NULL if
your application requires them but do not set columns as NULLable by default.
Using a constant value which falls outside domain range for the column instead
of NULL can speed up the look ups.

Date and time

Setting record creation date and record update date to DATETIME is a favorite
among database designer. Most people are not aware that DATETIME is among the
costliest data types in terms of space. DATETIME consumes 8 bytes of space. Date
and time types should be avoided unless you need to compare dates in your quries.

If you have to store dates for display purpose only, storing Unix time stamp
as a MEDIUMINT can surve the purpose. All programing languages provide mechanism
for creating current Unix time stamp and converting Unix time stamp back to date
format. So, 1223890532 will result in faster look up compared to 2008-10-13
05:35:32.

CHAR or VARCHAR ?

CHAR is fixed width and VARCHAR is variable length. But VARCHAR requires
extra byte to store the record. If length of VARCHAR is 255 or less it requires
one byte but if the length of VARCHAR is greater than 255 bytes it requires two
bytes. A VARCHAR is store as one-byte or two-byte length prefix plus data.

The length prefix contains the number of bytes in the record. A field
declared as VARCHAR(5) may take 6 bytes instead of 5 bytes as expected.

Finding the correct data type

MySQL comes with a built-in procedure to analyze a query and suggests the
optimal data types for each column. This procedure is called "Procedure Analyse".

Procedure analyse will analyze the existing data in a table and try to guess
the optimal data type based on the distinct values per column present in the
table.

The syntax for this procedure is following;

SELECT col1, col2 FROM table1 PROCEDURE
ANALYSE(>);

Procedure Analyze expects two optional parameters max_elements and max_memory.
max_memory is maximum memory that shall be allocated on per column basis while
trying to find all distinct values for a column.

max_elements defaults to a value of 256 and is used to identify the maximum
number of distinct values analyse will use per column. Distinct values are used
to identify if ENUM is a

suitable data type for a given column.

To test procedure analyse we created a table called user_master here is the
description of the table

mysql> desc user_master ;

+-----------------+---------------------+---

| Field | Type | Null | Key | Default | Extra |

+-----------------+---------------------+---

| user_id | int(11) | NO | PRI | | auto_increment |

| first_name | varchar(20) | YES | | NULL | |

| last_name | varchar(20) | YES | | NULL | |

+-----------------+----------------------+--

3 rows in set (0.00 sec)

We loaded the table with 16 users but ensured that first_name of the users is
"Pcquest" or "Preview" or "Test". We also ensured every user has unique
last_name. Following command was used to analyze the table and identify the
optimal data type.

mysql> select user_id,first_name,last_name from
user_master procedure analyse(10,50)\G

********* Row 1*****************

Field_name: mydb.user_master.user_id

Min_value: 1

Max_value: 16

Min_length: 1

Max_length: 2

Empties_or_zeros: 0

Nulls: 0

Avg_value_or_avg_length: 8.5000

Std: 4.6098

Optimal_fieldtype: TINYINT(2) UNSIGNED NOT NULL

**************Row 2********** ***

Field_name: mydb.user_master.first_name

Min_value: Pcquest

Max_value: Test

Min_length: 4

Max_length: 7

Empties_or_zeros: 0

Nulls: 0

Avg_value_or_avg_length: 4.1875

Std: NULL

Optimal_fieldtype: ENUM('Pcquest','Preview','Test') NOT NULL

*************Row 3************ Field_name: mydb.user_master.last_name

Min_value: Usr1

Max_value: User101

Min_length: 4

Max_length: 7

Empties_or_zeros: 0

Nulls: 0

Avg_value_or_avg_length: 4.1875

Std: NULL

Optimal_fieldtype: VARCHAR(7) NOT NULL

As you can notice the optimal data type suggested for user_id is TINYINT, as
for only 16 users INT type is an overkill. TINYINT will consume 1 byte where as
INT will consume 4 bytes, a saving of 75% of space and memory.

Procedure Analyse at times reports optimal data type as ENUM where you least
expect. In the example above first_name is suggested to be ENUM as procedure
analyse will only see 3 distinct values in 16 records. As procedure analyse
works on existing records in the table and distinct records in the current data
set, ENUM will the most optimal data type for first_name.

By trying different values to procedure analyse, more suggestions about optimal
data type can be obtained. You may have to play with settings till procedure
analyse does not return ENUM data type when it is not appropriate.

On a fairly large record set, results of procedure analyse are very useful.

Vivek Khurana