Partition Tables in MySQL
Last Updated on: October 11, 2022
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 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 – a form of hash with even distribution of data
- List – segments data on a 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:
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 );
This will create a table called mypartitiontable with three partitions depending on the year stored in the field c3.
Get notified of new posts: