Does mysql horizontal partitioning create new ibd files for each partition?

Posted on

Question :

To my best knowledge, each table created in mysql creates an ibd file as a pointer to your table. Any alteration made to this table happens on this ibd file.
Now if Table1 of size 1GB, with its ibd file x.ibd, is horizontally partitioned equally into p1,p2,p3,p4. Do the new partitions p1,p2,p3 and p4 having equal size around 250MB each have their own ibd files? If no, how can we do it?

Answer :

Short answer: Yes; 4 .ibd files of about 1/4 the total size.

Long answer:

There are multiple things going on…

  • Different engines have PARTITIONs implemented. In all cases from 5.1 (when Partitioning was introduced) through at least 5.7, a partition is effectively a separate table.
  • innodb_file_per_table controls whether InnoDB puts new tables (and its partitions) in the ibdata1 file, or in individual .ibd file(s).
  • “Native partitions” may be coming soon (5.8?); that will change much of this answer.
  • Depending on the data distrubution and the details of the PARTITION clause in your CREATE TABLE, the partitions are not necessarily similar size.

In my experience, PARTITION BY RANGE is the only useful one, and there are only 4 use cases where it is of any use. Details.

If you execute a simple query like this:

CREATE TABLE partitioned
     column1 VARCHAR(12))


You will see that it does create separate IBD files for each of the partitions (At least it does so on mysql 5.7 can’t recall the behavior on older versions)

Leave a Reply

Your email address will not be published. Required fields are marked *