mySQL KEY Partitioning using three table fields (columns)

Question

I am writing a data warehouse, using MySQL as the back-end. I need to partition a table based on two integer IDs and a name string. I have read (parts of) the mySQL documentation regarding partitioning, and it seems the most appropriate partitioning scheme in this scenario would be either a HASH or KEY partitioning.

I have elected for a KEY partitioning because I (chicked out and) dont want to be responsible for providing a 'collision free' hashing algorithm for my fields - instead, I am relying on MySQL hashing to generate the keys required for hashing.

I have included below, a snippet of the schema of the table that I would like to partition based on the COMPOSITE of the following fields:

school id, course_id, ssname (student surname).

BTW, before anyone points out that this is not the best way to store school related information, I'll have to point out that I am only using the case below as an analogy to what I am trying to model.

My Current CREATE TABLE statement looks like this:

CREATE TABLE foobar (
    id         int UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    school_id  int UNSIGNED NOT NULL,
    course_id  int UNSIGNED NOT NULL,
    ssname     varchar(64) NOT NULL,

    /* some other fields */

    FOREIGN KEY (school_id) REFERENCES school(id) ON DELETE RESTRICT ON UPDATE CASCADE,

    FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE RESTRICT ON UPDATE CASCADE,

    INDEX idx_fb_si (school_id),
    INDEX idx_fb_ci (course_id),
    CONSTRAINT UNIQUE INDEX idx_fb_scs (school_id,course_id,ssname(16))
) ENGINE=innodb;

I would like to know how to modify the statement above so that the table is partitioned using the three fields I mentioned at the begining of this question (namely - school_id, course_id and the starting letter of the students surname).

Another question I would like to ask is this:

What happens in 'edge' situations for example if I attempt to insert a record that contains a valid* school_id, course_id or surname - for which no underlying partitioned table file exists - will mySQL automatically create the underlying file.?

Case in point. I have the following schools: New York Kindergaten, Belfast Elementary and the following courses: Lie Algebra in Infitesmal Dimensions, Entangled Entities

Also assume I have the following students (surnames): Bush, Blair, Hussein

When I add a new school (or course, or student), can I insert them into the foobar table (actually, I cant think why not). The reason I ask is that I forsee adding more schools and courses etc, which means that mySQL will have to create additional tables behind the scenes (as the hash will generate new keys).

I will be grateful if someone with experience in this area can confirm (preferably with links backing their assertion), that my understanding (i.e. no manual administration is required if I add new schools, courses or students to the database), is correct.

I dont know if my second question was well formed (clear) or not. If not, I will be glad to clarify further.

*VALID - by valid, I mean that it is valid in terms of not breaking referential integrity.

Answer 1

I doubt partitioning is as useful as you think. That said, there are a couple of other problems with what you're asking for (note: the entirety of this answer applies to MySQL 5; version 6 might be different):

• columns used in KEY partitioning must be a part of the primary key. school_id, course_id and ssname are not part of the primary key.
• more generally, every UNIQUE key (including the primary key) must include all columns in the partition¹. This means you can only partition on the intersection of the columns in the UNIQUE keys. In your example, the intersection is empty.
• most partitioning schemes (other than KEY) require integer or null values. If not NULL, ssname will not be an integer value.
• foreign keys and partitioning aren't supported simultaneously². This is a strong argument not to use partitioning.

Fortunately, collision free hashing is one thing you don't need to worry about, because partitioning is going to result in collisions (otherwise, you'd only have a single row in each partition). If you could ignore the above problems as well as the limitations on functions used in partitioning expressions, you could create a HASH partition with:

CREATE TABLE foobar (
    ...
) ENGINE=innodb
  PARTITION BY HASH (school_id + course_id + ORD(ssname))
  PARTITIONS 2
;

What should work is:

CREATE TABLE foobar (
    id         int UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id  int UNSIGNED NOT NULL,
    course_id  int UNSIGNED NOT NULL,
    ssname     varchar(64) NOT NULL,

    /* some other fields */

    PRIMARY KEY (id, school_id, course_id),
    INDEX idx_fb_si (school_id),
    INDEX idx_fb_ci (course_id),
    CONSTRAINT UNIQUE INDEX idx_fb_scs (school_id,course_id,ssname)
) ENGINE=innodb
      PARTITION BY HASH (school_id + course_id)
      PARTITIONS 2
;

or:

CREATE TABLE foobar (
    id         int UNSIGNED NOT NULL AUTO_INCREMENT,
    school_id  int UNSIGNED NOT NULL,
    course_id  int UNSIGNED NOT NULL,
    ssname     varchar(64) NOT NULL,

    /* some other fields */

    PRIMARY KEY (id, school_id, course_id, ssname),
    INDEX idx_fb_si (school_id),
    INDEX idx_fb_ci (course_id),
    CONSTRAINT UNIQUE INDEX idx_fb_scs (school_id,course_id,ssname)
) ENGINE=innodb
      PARTITION BY KEY (school_id, course_id, ssname)
      PARTITIONS 2
;

As for the files that store tables, MySOL will create them, though it may do it when you define the table rather than when rows are inserted into it. You don't need to worry about how MySQL manages files. Remember, there are a limited number of partitions, defined when you create the table by the PARTITIONS *n* clause.