CREATE TABLESPACE tablespace_name
    ADD DATAFILE 'file_name'
    USE LOGFILE GROUP logfile_group
    [EXTENT_SIZE [=] extent_size]
    [INITIAL_SIZE [=] initial_size]
    [AUTOEXTEND_SIZE [=] autoextend_size]
    [MAX_SIZE [=] max_size]
    [NODEGROUP [=] nodegroup_id]
    [WAIT]
    [COMMENT [=] comment_text]
    ENGINE [=] engine_name

This statement is used to create a tablespace, which can contain one or more data files, providing storage space for tables. One data file is created and added to the tablespace using this statement. Additional data files may be added to the tablespace by using the ALTER TABLESPACE statement (see Section 12.1.5, “ALTER TABLESPACE Syntax”). For rules covering the naming of tablespaces, see Section 8.2, “Schema Object Names”.

A log file group of one or more UNDO log files must be assigned to the tablespace to be created with the USE LOGFILE GROUP clause. logfile_group must be an existing log file group created with CREATE LOGFILE GROUP (see Section 12.1.8, “CREATE LOGFILE GROUP Syntax”). Multiple tablespaces may use the same log file group for UNDO logging.

The EXTENT_SIZE sets the size, in bytes, of the extents used by any files belonging to the tablespace. The default value is 1M. The minimum size is 32K, and the theoretical maximum is 2G, although the practical maximum size depends on a number of factors.

An extent is a unit of disk space allocation. One extent is filled with as much data as that extent can contain before another extent is used. In theory, up to 65,535 (64K) extents may used per data file; however, the recommended maximum is 32,768 (32K). The recommended maximum size for a single data file is 32G — that is, 32K extents × 1 MB per extent. Smaller extents have the advantage that they tend to provide lower latency; however, larger extents tend to allow for greater throughput. You must also take into consideration that larger extents may mean longer node restart times. In addition, once an extent is allocated to a given table, it cannot be used to store data from another; an extent cannot store table from more than one table. This means, for example that a tablespace having a single datafile whose INITIAL_SIZE is 256 MB and whose EXTENT_SIZE is 128M has just two extents, and so can be used to store data from at most two different disk data tables.

You can see how many extents remain free in a given data file by querying the INFORMATION_SCHEMA.FILES table, and so derive an estimate for how much space remains free in the file. For further discussion and examples, see Section 26.21, “The INFORMATION_SCHEMA FILES Table”.

The INITIAL_SIZE parameter sets the data file's total size in bytes. Once the file has been created, its size cannot be changed; however, you can add more data files to the tablespace using ALTER TABLESPACE ... ADD DATAFILE. See Section 12.1.5, “ALTER TABLESPACE Syntax”.

INITIAL_SIZE is optional; its default value is 128M.

On 32-bit systems, the maximum supported value for INITIAL_SIZE is 4G. (Bug#29186)

When setting EXTENT_SIZE or INITIAL_SIZE (either or both), you may optionally follow the number with a one-letter abbreviation for an order of magnitude, similar to those used in my.cnf. Generally, this is one of the letters M (for megabytes) or G (for gigabytes).

AUTOEXTEND_SIZE, MAX_SIZE, NODEGROUP, WAIT, and COMMENT are parsed but ignored, and so have no effect in MySQL 5.1. These options are intended for future expansion.

The ENGINE parameter determines the storage engine which uses this tablespace, with engine_name being the name of the storage engine. In MySQL 5.1, engine_name must be one of the values NDB or NDBCLUSTER.

When CREATE TABLESPACE is used with ENGINE = NDB, a tablespace and associated data file are created on each Cluster data node. You can verify that the data files were created and obtain information about them by querying the INFORMATION_SCHEMA.FILES table. For example:

mysql> SELECT LOGFILE_GROUP_NAME, FILE_NAME, EXTRA 
    -> FROM INFORMATION_SCHEMA.FILES
    -> WHERE TABLESPACE_NAME = 'newts' AND FILE_TYPE = 'DATAFILE';
+--------------------+-------------+----------------+
| LOGFILE_GROUP_NAME | FILE_NAME   | EXTRA          |
+--------------------+-------------+----------------+
| lg_3               | newdata.dat | CLUSTER_NODE=3 |
| lg_3               | newdata.dat | CLUSTER_NODE=4 |
+--------------------+-------------+----------------+
2 rows in set (0.01 sec)

(See Section 26.21, “The INFORMATION_SCHEMA FILES Table”.)

CREATE TABLESPACE was added in MySQL 5.1.6. In MySQL 5.1, it is useful only with Disk Data storage for MySQL Cluster. See Section 19.12, “MySQL Cluster Disk Data Tables”.