Creating Partitioned Tables

This topic describes creating of partitioned tables of the following types supported in MySQL:

  • Range-partitioned
  • Range column-partitioned
  • Hash-partitioned
  • List-partitioned
  • List columns-partitioned
  • Key-partitioned
  • Composite partitioned

To learn more about working with partitioned tables and their types in MySQL, see Working with Partitioned Tables.

Creating a Range-Partitioned Table

  1. Click the Create New Database Object button and select type Table from the list.
  2. Input the table name.
  3. Click Create and, if the specified name is unique, the table will appear in Database Explorer.
  4. Add required table columns to the table and set table type and required parameters.
  5. Optionally, you can alter the name or change database (owner) in which the new table will be created.
  6. Switch to the Partitioning tab.
  7. Select the partitioning type Range.
  8. Specify the Partitioning expression.

  9. Add required partitions and set values for partitions’ bounds for all partition columns.

  10. Set other parameters (Comment, Storage engine) for partitions if necessary.
  11. Save the document.

Creating a Range Column-Partitioned Table

  1. Click the Create New Database Object button and select type Table from the list.
  2. Input the table name.
  3. Click Create and, if the specified name is unique, the table will appear in Database Explorer.
  4. Add required table columns to the table and set table type and required parameters.
  5. Optionally, alter the name or change database (owner) in which the new table will be created.
  6. Switch to the Partitioning tab.
  7. Select the partitioning type Range Columns.
  8. Specify the Partitioning columns.

  9. Add required partitions and set values for partitions’ bounds for all partition columns.

  10. Set other parameters (Comment, Storage engine) for partitions if necessary.
  11. Save the document.

Creating a Hash-Partitioned Table

  1. Click the Create New Database Object button and select type Table from the list.

  • or -

    In Database Explorer, right-click the Tables node and select New Table.

  1. Input the table name.
  2. Click Create and, if specified name is unique, the table will appear in Database Explorer.
  3. Add required table columns to the table and set table type and parameters.
  4. Optionally, alter the name or change database (owner) in which the new table will be created.
  5. Switch to the Partitioning tab.
  6. Select the partitioning type Hash (Linear Hash).
  7. Specify the Partitioning expression. If the table has a primary key or unique key with NOT NULL columns, you should use only the columns of this key.
  8. Add required partitions.
  9. Set other parameters (Comment, Storage engine) for partitions if necessary.
  10. Save the document.

Creating a List-Partitioned Table

  1. Click the Create New Database Object button and select type Table from the list.

  • or -

    In Database Explorer, right-click the Tables node and select New Table.

  1. Input the table name.
  2. Click Create and, if specified name is unique, the table will appear in Database Explorer.
  3. Add required table columns to the table and set table type and required parameters.
  4. Optionally, alter the name or change database (owner) in which the new table will be created.
  5. Switch to the Partitioning tab.
  6. Select the partitioning type List.
  7. Specify the Partitioning expression.

  8. Add required partitions and set list values for partitions.

  9. Set other parameters (Comment, Storage engine) for partitions if necessary.
  10. Save the document.

Creating a List Columns-Partitioned Table

  1. Click the Create New Database Object button and select type Table from the list.

  • or -

    In Database Explorer, right-click the Tables node and select New Table.

  1. Input the table name.
  2. Click Create and, if specified name is unique, the table will appear in Database Explorer.
  3. Add required table columns to the table and set table type and required parameters.
  4. Optionally, alter the name or change database (owner) in which the new table will be created.
  5. Switch to the Partitioning tab.
  6. Select the partitioning type List Columns.
  7. Specify the Partitioning columns.

  8. Add required partitions and set list values for partitions.

  9. Set other parameters (Comment, Storage engine) for partitions if necessary.
  10. Save the document.

Creating a Key-Partitioned Table

  1. Click the Create New Database Object button and select type Table from the list.

    • or -

    In Database Explorer, right-click the Tables node and select New Table.

  2. Input the table name.
  3. Click Create and, if specified name is unique, the table will appear in Database Explorer.
  4. Add required table columns to the table and set table type and parameters you need.
  5. Optionally, alter the name or change database (owner) in which the new table will be created.
  6. Switch to the Partitioning tab.
  7. Select partitioning type Key.
  8. Select Partitioning columns. If the table has a primary key or unique key with NOT NULL columns, you should use only the columns of this key.
  9. Add required partitions.
  10. Set other parameters (Comment, Storage engine) for partitions if necessary.
  11. Save the document.

Note

Linear hash and linear key partitioned tables can be created in the same way as hash and key partitioned tables respectively. Just select Linear hash instead of Hash or Linear key instead of Key in the Partitioning type drop-down list.

In a composite partitioned table, partitions are divided into subpartitions. Subpartitions can be set individually for each partition.

Creating a Composite Partitioned Table

  1. Click the Create New Database Object button and select type Table from the list.
  2. Input the table name.
  3. Click Create and, if specified name is unique, the table will appear in Database Explorer.
  4. Add required table columns to the table and set table type and parameters you need.
  5. Optionally, alter the name or change database (owner) in which the new table will be created.
  6. Switch to the Partitioning tab.
  7. Select partitioning type Range, Range Columns, List or List Columns.
  8. Select subpartitioning type.
  9. Specify the Partitioning expression.
  10. Select Subpartitioning columns for the key subpartitioning or specify the Subpartitioning expression for hash subpartitioning. If the table has a primary key or unique key with NOT NULL columns, you should use only the columns of this key. For key partitioning, you may skip the Subpartitioning columns setting, the key columns will be used in that case.
  11. Add required partitions and set values for partitions’ bounds for all partition columns.

  12. Add subpartitions manually if required or set their quantity for partitions. Each partition must have the same number of subpartitions.

    Note

    In MySQL 5.1.8 and higher subpartition names must be unique across the entire table.

  13. Set tablespace names for partitions and subpartitions if required.
  14. Save the document.

Note

Linear hash and linear key subpartitioned tables can be created in the same way as hash and key subpartitioned tables respectively. Just choose Linear hash instead of Hash or Linear key instead of Key in the Subpartitioning type drop-down list.