Partitioning a database table in MySQL

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.

A more concrete example would be to assume that I am storing data about a school. I want to partition the school_data table based on COMPOSITE 'Key' based on the following:

school id (integer)
course_id (integer)
student_surname (string)

For the student surname, it is just the first character of the surname that determines which 'partitioned table' the data should go in to.

How may I implement this requirement using MySQL (5.1) with InnoDb tables?

Also, I am doing my development on a Windows box, but I will deploy onto a *nix box for production. I have two further questions:

1. I am assuming that I will have to dump and restore the data when moving from Windows to Linux. I don't know if this is OK if the database contains partitioned tables (pointer to where it states this in the documentation will put my mind to rest - I have not been able to find any specific mention of dump/restore regarding partitioned tables.
2. I may also need to change databases (if Oracle pulls a surprise move on MySQL users) in which case I will need to SOMEHOW export the data into another database. In this (hopefully unlikely scenario) - what will be the best way to dump data out of MySQL (maybe to text or something) bearing in mind the partitioned table?

Answer 1

RANGE Partitioning

A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.

CREATE TABLE employees (
  school id (integer)
  course_id (integer)
  student_surname (string)
)
  PARTITION BY RANGE (student_surname) (
  PARTITION p0 VALUES LESS THAN ('ezzzzzzzzzzzzzzzzzzzzzzz'),
  PARTITION p1 VALUES LESS THAN ('ozzzzzzzzzzzzzzzzzzzzzzz'),
  PARTITION p2 VALUES LESS THAN ('tzzzzzzzzzzzzzzzzzzzzzzz'),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Range partitioning

Data Migration to Another DB

MySQLDUMP will output the table and data to a file. However, Oracle supports connecting to other databases via ODBC, just as SQL Server has it's linked server capability.

Addendum

It looks like you are partitioning by only one of the 3 fields I mentioned (i.e. name). I saw partitioning by a single field in the MySQL docs, but not 3 fields (int, int, string) like I want to do.

Partitioning by three columns is possible, but my example is per your requirements in the OP:

For the student surname, it just the first character of the surname that determines which 'partitioned table' the data should go in to.

Answer 2

How may I implement this requirement using mySQL (5.1) with InnoDb tables?

Have a look at the Chapter 18. Partitioning of MySQL documentation and especially the Partition Types (I'd look at the HASH partitioning). But keep in mind that the partitioning implementation in MySQL 5.1 is still undergoing development and there are some limitations and restrictions.

I am assuming that I will have to dump and restore the data when moving from windows to Linux. I dont know if this is OK if the db contains partitioned tables (pointer to where it states this in the docs will put my mind to rest - I have not been able to find any specific mention of dump/restore regarding partitioned tables.

I didn't find anything in 18.3 Partition Management but, according to this post, backing up and restoring a partitioned table is nothing special. To backup:

mysqldump --opt db_name table_name > file.dump

And to restore:

mysql db_name < file.dump

I would do some testing though.

I may also need to change databases (if Oracle pulls a suprise move on mySQL users) in which case I will need to SOMEHOW export the data into another database. In this (hopefully unlikely scenario) - what will be the best way to dump data out of mySQL (maybe to text or something) bearing in mind the partitioned table?

Oracle SQL Developer incorporates migration support by including redeveloped features and greatly extending the functionality and usability offered by the original Oracle Migration Workbench to migrate Microsoft Access, Microsoft SQL Server, MySQL and Sybase databases to Oracle.