Prerequisites

It must be MySQL 5.1 or later. Then, to see if partitioning is available:

SHOW VARIABLES LIKE '%partition%';

or

SHOW PLUGINS;

If neither of these queries mention partitions, be sure to recompile with the --with-partition option.

Setting up a table

There are several types of partitions, but LIST seems most compatible with partitioning by date. Let’s setup a core table:

CREATE TABLE core(
  core_id INT NOT NULL AUTO_INCREMENT,
  stamp DATETIME,
  data VARCHAR(80),
  PRIMARY KEY(core_id,stamp)
)

PARTITION BY LIST(TO_DAYS(stamp))(
  PARTITION core_20100715 VALUES IN (TO_DAYS('2010-07-15')),
  PARTITION core_20100716 VALUES IN (TO_DAYS('2010-07-16'))
);

Notice that the primary key must include stamp or MySQL will give an error.

Testing the partition

Now a simple insert:

INSERT INTO core (stamp,data) VALUES ('20100715','a')

This should work.

Now a simple insert outside of the existing partitions:

INSERT INTO core (stamp,data) VALUES ('20100717','x')

Gives this error:

SQL Error (1526): Table has no partition for value

Read the information_schema

It’s possible to see the existing partitions in the information_schema database:

SELECT *
FROM information_schema.PARTITIONS
WHERE TABLE_NAME='core';

Which shows us: | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PARTITION_NAME | SUBPARTITION_NAME | PARTITION_ORDINAL_POSITION | SUBPARTITION_ORDINAL_POSITION | PARTITION_METHOD | SUBPARTITION_METHOD | PARTITION_EXPRESSION | SUBPARTITION_EXPRESSION | PARTITION_DESCRIPTION | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | CREATE_TIME | UPDATE_TIME | CHECK_TIME | CHECKSUM | PARTITION_COMMENT | NODEGROUP | TABLESPACE_NAME | |—————|————–|————|—————-|——————-|—————————-|——————————-|——————|———————|———————-|————————-|———————–|————|—————-|————-|—————–|————–|———–|————-|————-|————|———-|——————-|———–|—————–| | NULL | test | core | core_20100715 | NULL | 1 | NULL | LIST | NULL | TO_DAYS(stamp) | NULL | 734333 | 0 | 0 | 16384 | NULL | 0 | 4194304 | NULL | NULL | NULL | NULL | | default | NULL | | NULL | test | core | core_20100716 | NULL | 2 | NULL | LIST | NULL | TO_DAYS(stamp) | NULL | 734334 | 0 | 0 | 16384 | NULL | 0 | 4194304 | NULL | NULL | NULL | NULL | | default | NULL |

Adding and Removing Individual Partitions

Here’s how to add additional partitions:

ALTER TABLE core
ADD PARTITION (PARTITION core_20100717 VALUES IN (TO_DAYS('2010-07-17')));

Here’s how to remove a partition:

ALTER TABLE core
DROP PARTITION core_20100717;

Adding and Removing Partitioning on Existing Tables

If the table was not originally partitioned, it is possible to add it:

ALTER TABLE core
PARTITION BY LIST(TO_DAYS(stamp))(
  PARTITION core_20100715 VALUES IN (TO_DAYS('2010-07-15')),
  PARTITION core_20100716 VALUES IN (TO_DAYS('2010-07-16'))
);

And remove it:

ALTER TABLE core
REMOVE PARTITIONING;

Adding and removing partitions doesn’t seem to change existing data on the table, but new partitions must include all existing data.

Storing a Partition on a Separate Directory

Each partition may be stored on a separate data and index directory:

ALTER TABLE core
PARTITION BY LIST(TO_DAYS(stamp))(
  PARTITION p20100715 VALUES IN (TO_DAYS('2010-07-15'))
  DATA DIRECTORY '/data/2010-07-15'
  INDEX DIRECTORY '/data/2010-07-15',
  PARTITION p20100716 VALUES IN (TO_DAYS('2010-07-16'))
  DATA DIRECTORY '/data/2010-07-16'
  INDEX DIRECTORY '/data/2010-07-16'
);

Note, DATA DIRECTORY and INDEX DIRECTORY are ignored on Windows after MySQL 5.1.24.

You may get this error: SQL Error (1210): Incorrect arguments to DATA DIRECTORY

If so, it’s likely because the DATA DIRECTORY is already being used by another part of MySQL. The partition data directories can’t be subdirectories of MySQL’s current data directory. The best thing to do is make a new directory maybe mysql_part that is easily accessible.

Partition Naming between Tables

Different tables can use the same names for their partition. Consider the following example:

CREATE TABLE core(
  core_id INT NOT NULL AUTO_INCREMENT,
  stamp DATETIME,
  data VARCHAR(80),
  PRIMARY KEY(core_id,stamp)
)
PARTITION BY LIST(TO_DAYS(stamp))(
  PARTITION p20100715 VALUES IN (TO_DAYS('2010-07-15')),
  PARTITION p20100716 VALUES IN (TO_DAYS('2010-07-16'))
);

CREATE TABLE core2(
  core_id INT NOT NULL AUTO_INCREMENT,
  stamp DATETIME,
  data VARCHAR(80),
  PRIMARY KEY(core_id,stamp)
)
PARTITION BY LIST(TO_DAYS(stamp))(
  PARTITION p20100715 VALUES IN (TO_DAYS('2010-07-15')),
  PARTITION p20100716 VALUES IN (TO_DAYS('2010-07-16'))
);

Both tables use the exact same partition names: p20100715 and p20100716. No error occurs.

See also

• MySQL 5.1 Documentation: Partitioning
• MySQL 5.1 Documentation: CREATE TABLE
• MySQL 5.1 Documentation: ALTER TABLE