CREATE TABLE - Partitioning

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
PARTITION BY
  [LINEAR] HASH(expression) | 
  [LINEAR] KEY(columns) | 
  RANGE(expression) | 
  LIST(expression)
  [PARTITIONS number]
    [SUBPARTITION BY
      [LINEAR] HASH(expression) | 
      [LINEAR] KEY(columns)
      [SUBPARTITIONS number]
    ]
  [PARTITION partition
        [VALUES {LESS THAN (expression)|MAXVALUE|IN (values)}]
        [[STORAGE] ENGINE [=] engine]
        [COMMENT [=] 'text' ]
        [DATA DIRECTORY [=] '/path']
        [INDEX DIRECTORY [=] '/path']
        [MAX_ROWS [=] number]
        [MIN_ROWS [=] number]
        [TABLESPACE [=] (tablespace)]
        [NODEGROUP [=] value]

        [(SUBPARTITION logical_name
           [[STORAGE] ENGINE [=] engine]
           [COMMENT [=] 'text' ]
           [DATA DIRECTORY [=] '/path']
           [INDEX DIRECTORY [=] '/path']
           [MAX_ROWS [=] number]
           [MIN_ROWS [=] number]
           [TABLESPACE [=] (tablespace)]
           [NODEGROUP [=] value]
        [, SUBPARTITION...])]
  [, PARTITION...]]
  ]

Explanation

These table partition clauses may be used in CREATE TABLE to create a table using partitions, that is to say, to organize data into seperate files on the filesystem. This capability was added as of version 5.1.6 of MySQL. To add or alter partitions on an existing table, see the ALTER TABLE - Partition altering clauses explanation for comments on partitions in general. On this page, several examples are included of creating a MyISAM table with partitions.

The PARTITION BY clause is required when partitioning in order to explain how data is split up and distributed among partitions. A table cannot have more than 1024 partitions and subpartitions. The subclauses of the PARTITION BY are explained on this documentation page, whereas the PARTITION is described on the Partition definitions and SUBPARTITION clauses are explained on the Sub-Partition definitions page related to this statement.

HASH

This subclause creates a key/value pair that controls which partition is used for saving rows of data and for indexing data. The value of the hash consists of the specified columns. If a table has a primary key, that column must be used by the hash. Functions that return a numerical value (not a string) may be used within a hash specification.

CREATE TABLE sales_figures
(emp_id INT, 
sales_date DATE,
amount INT)
PARTITION BY HASH(MONTH(sales_date))
PARTITIONS 12;

This creates twelve partitions, one for each month extracted from the sales_data.

By default, the HASH method and the KEY method (described later) use the modulus of the hash function's given value. The keyword LINEAR may be added in front of HASH or KEY to change the algorithm to a linear powers-of-two algorithm. For extremely large tables of data, the linear hash has higher performance results in processing data, but does not evenly spread data amongst partitions.

KEY

This subclause functions the same as HASH except that it accepts only a comma-separated list of columns for indexing and distributing data amongst partitions. The LINEAR flag may be given to change the algorithm method used. See the previous desciption for HASH.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
PARTITION BY KEY (client_id)
PARTITIONS 4;

LIST

This subclause can be used to give specific values for distributing data across partitions. The column and values must must all be numeric, not strings.

CREATE TABLE sales_figures
(region_id INT, sales_date DATE, amount INT)
PARTITION BY LIST (region_id) (
   PARTITION US_DATA VALUES IN(100,200,300),
   PARTITION EU_DATA VALUES IN(400,500));

In this example, data is distributed between two partitions: one for the sales in the United States, which is composed of three regions, and a second partition for data for the two European regions. Notice that the names for the partitions given aren't in the usual naming convention (e.g., p0). Any name will do. It's a matter of preference.

RANGE

To instruct MySQL to distribute data among the partitions based on a range of values, use the RANGE subclause. Use the VALUES LESS THAN subclause to set limits for each range. Use VALUES LESS THAN MAXVALUE to set the limit of final partition.

CREATE TABLE clients
(client_id INT AUTO_INCREMENT KEY, 
client_name VARCHAR(75), 
telephone CHAR(15))
PARTITION BY RANGE (client_id) (
   PARTITION p0 VALUES LESS THAN (500),
   PARTITION p1 VALUES LESS THAN (1000),
   PARTITION p3 VALUES LESS THAN MAXVALUE);

In this example, the data is distributed among the partitions based on the client_id values. The first partion will contain rows with a client identification number less than 500, the second will contain rows where the values ranges from 501 to 1000, and the last partition will contain values of 1000 and higher. Values given for partitions must be in order.

See the ALTER TABLE explanation for more information on table partitioning, especially modifying or removing partitioning.