If @@innodb_file_per_table is set to OFF when the table is partitioned, all partitions are stored in the InnoDB system tablespace, but in different areas. If this variable is set to 1, each partition is stored in a different file.

Storage engines that store data and indexes in separate files, such as Aria and MyISAM, create a data file and an index file for each partition.

The extension of partition data or index files is the same that is used for unpartitioned tables. The basename of the files is the name of the table, plus #P#, plus the name of the partition. So, the name pattern is as follows:

<table_name>#P#<partition_name>.<extension>

Like all tables, partitioned tables have a .frm file that contains the table definition. They also have a file with partitions' definitions, which has a .par extension.

For example, let's suppose we have an InnoDB table called employee, with two partitions called p0 and p1. We will have the following files:

employee.frm
employee.par
employee#P#p0.ibd
employee#P#p1.ibd

If it is an Aria table, we will have the following files:

employee.frm
employee.par
employee#P#p0.MAD
employee#P#p0.MAI
employee#P#p1.MAD
employee#P#p1.MAI

For subpartitioned tables, each subpartition has a separate file. The basename of these files is the name of the table, plus the partition suffix, plus a subpartition suffix. For example, let the previous InnoDB employee table have two subpartitions per partition, called s0, s1, s2, and s3. We will see the following files:

employee.frm
employee.par
employee#P#p0#SP#s0.ibd
employee#P#p0#SP#s1.ibd
employee#P#p1#SP#s2.ibd
employee#P#p1#SP#s3.ibd

When setting the value of @@table_open_cache (explained in Chapter 6, Caches), we must remember that each partition will require a separate file handle.

By default, partitions' files are stored in the database path in the data directory. It is possible to specify a path for each partition's data file and index file. By doing so, we can distribute the partitions through several disks, reducing the overhead of disks input and output. This feature is very useful, and it generally is the main reason why we may want to use partitioning. The syntax to do this is the following:

CREATE TABLE employee (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
)
ENGINE = InnoDB
PARTITION BY RANGE (id) (
    PARTITION p0 VALUES LESS THAN (10000)
  DATA DIRECTORY '/disk_x',
    PARTITION p1 VALUES LESS THAN MAXVALUE
);

In this example, a database directory will be created on disk_x if it does not exist, and it will contain the data file for partition p0. Even if all partitions are stored on nonstandard paths, the data directory will still contain the .par file.