title: MySQL partitioning with ActiveRecord
body: <p>I want to take advantage of the new partitioning in MySQL 5.1 but using a standard ActiveRecord model.  The problem I have encountered is that to use the partitioning the primary key on the table must include the columns used in the partitioning function.  (<a href="http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html" rel="nofollow noreferrer">http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-keys.html</a>)</p>
  
  <p>Now, I want to have a auto-incrementing 'id' field (which is usually used as the primary key in Rails) but I want to partition using another column, in this case 'task_id'.  Crucially I don't want to use composite primary keys in my Rails application.  My question is this:</p>
  
  <p>If I set up the table as follows:</p>
  
  <pre><code>"CREATE TABLE `annotations` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `task_id` int(11) DEFAULT NULL,
    `user_id` int(11) DEFAULT NULL,
    `value` text,
    `created_at` datetime DEFAULT NULL,
    `updated_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`, `task_id`),
    KEY `index_annotations_on_task_id` (`task_id`),
    KEY `index_annotations_on_user_id` (`user_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
  PARTITION BY RANGE(task_id)(
  PARTITION 1_to_5 VALUES LESS THAN (6),
  PARTITION 6_to_10 VALUES LESS THAN (11),
  PARTITION 11_to_15 VALUES LESS THAN (16))"
  </code></pre>
  
  <p>where I have the primary key composed of the 'id' and 'task_id' columns, and I don't do anything special with the ActiveRecord model, am I setting myself up for any headaches in the future?  </p>
  
  <p>Basically, I'm creating a table with a primary key based upon two columns but not telling ActiveRecord about the second column as I still have an auto-incrementing id field - can anyone see any problems with this approach? </p>