MySQL, MariaDB partitioning

What is partitioning?

The structure which divides one table data to multiple data.
When not using partitioning, one table data is aggregated to one file. On the other hand, when using partitioning, one table data is divided to multiple files. Especially table which is referenced can focus on and select performance can be improved. Also, deleting unnecessary data is easier.

There are vertical partitioning and horizontal partitioning, but explains regarding horizontal partitioning in this section.

Regarding MySQL, partition functionality is attached from 5.1
Regarding MariaDB, partition functionality can be used from initial version, 5.1

Assumption of using partition

The used column needs to have primary key.

Kind of partitioning

Range

Specify range of column value.
e.g.

CREATE TABLE sample_table (
    id int NOT NULL,
    day DateTime NOT NULL
    ) ENGINE=InnoDB
    PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (3),

    PARTITION p0 VALUES LESS THAN (4)

)

List

Specify data list of column value.
e.g.

CREATE TABLE sample_table ( id int(2), day DateTime Not NULL ) ENGINE=InnoDB PARTITION BY LIST(id)( PARTITION p1 VALUES IN (1,2), PARTITION p2 VALUES IN (3,4,5));

Hash

RDBMS engine calculates hash and divides automatically.
e.g.

CREATE TABLE sample_table (
   id int(2),
   day DateTime NotNULL
 ) ENGINE=InnoDB
    PARTITION BY HASH(id)
    PARTITIONS 10;
;

Key

Specify more than one keys(primary or unique), and RDBMS engine create hash and divides automatically.
e.g.

CREATE TABLE t4 (
   id int(2) PRIMARY KEY,
   day DateTime NOT NULL
 ) ENGINE=InnoDB
    PARTITION BY KEY()
    PARTITIONS 10;
;