title: Partitioning a database table in MySQL
body: <p>I am writing a data warehouse, using <a href="http://en.wikipedia.org/wiki/MySQL" rel="nofollow noreferrer">MySQL</a> as the back-end. I need to partition a table based on two integer IDs and a name string.</p>
  
  <p>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:</p>
  
  <pre><code>school id (integer)
  course_id (integer)
  student_surname (string)
  </code></pre>
  
  <p>For the student surname, it is just the first character of the surname that determines which 'partitioned table'  the data should go in to.</p>
  
  <p>How may I implement this requirement using MySQL (5.1) with InnoDb tables?</p>
  
  <p>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:</p>
  
  <ol>
  <li>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.</li>
  <li>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?</li>
  </ol>