DevChunks

One of the big deciding factors in the performance of your database is the design of the database. Research from various parties, including Oracle has found that poor database design is a major factor in poor database performance and unscheduled system downtime. One great way to improve the performance of your MySQL database is to use the partitioning feature that was introduced in MySQL 5.1.

Partitioning is a physical database design technique that aims to reduce the size of the data read for SQL operations to improve the response time for the database. There are two types of partitioning:

  • Horizontal partitioning
  • Vertical partitioning

Horizontal partitioning segments table rows so that it makes groups of physical row-based datasets that you can address individually or collectively. Vertical partitioning reduces the width of a target table to include certain columns in a particular dataset.

MySQL now supports all the major forms of horizontal partitioning including:

  • Range – specify ranges to assign data
  • Hash – separates data based on a hash key
  • Key – form of hash with even distribution of data
  • List – segments data on pre-defined list of values
  • Composite – combines two or more partitioning modes

The two major advantages of partitioning are improved performance and simplified data management.

The following MySQL command would create a table with partitioning:

{code type=php}
Mysql> CREATE TABLE mypartitiontable
{ c1 int default NULL,
c2 varchar(30) default NULL,
c3 date default NULL
} engine=myslam
PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),
PARTITION p1 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN MAXVALUE );
{/code}

This will create a table called mypartitiontable with three partitions depending on the year stored in the field c3.

Share:

Leave a Reply

Your email address will not be published.

More Posts

How to use PHP Type Declarations

PHP has developed into a more strictly typed language in recent years. Type Declarations help with that and are a fairly new addition to PHP,

Iteration

If you have come to JavaScript from a background in Java or C, then you will like the variation on the for loop. It is

Context Object Manipulation

One of the key concepts of JavaScript is the keyword this, which signifies the JavaScript context object. If you write an inner function, you can access

Polymorphic Functions

It is possible to define multiple functions with the same name in JavaScript, and the last one of those that you define will be the