title: MySQL: Each index on InnoDB table takes longer to create than the last
body: <p>I've got a MySQL table that looks like this:</p>
  
  <pre><code>CREATE TABLE my_facts (
    `id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, 
    `account_id` int(11) NOT NULL, 
    `asked_on` date NOT NULL, 
    `foo_id` int(11) NOT NULL, 
    `bar_id` int(11) NOT NULL, 
    `baz_id` int(11) NOT NULL, 
    `corge_id` int(11) NOT NULL, 
    `grault_id` int(11) NOT NULL, 
    `flob_id` int(11) NOT NULL, 
    `tag_id` int(11) NOT NULL) 
  ENGINE=InnoDB;
  </code></pre>
  
  <p>and has 450k rows. But: I want to add several indexes to it:</p>
  
  <pre><code>CREATE INDEX `k_account_foo_id` ON `my_facts` (`account_id`, `asked_on`, `foo_id`, `tag_id`);
  
  CREATE INDEX `k_account_bar_id` ON `my_facts` (`account_id`, `asked_on`, `bar_id`, `tag_id`);
  
  CREATE INDEX `k_account_baz_id` ON `my_facts` (`account_id`, `asked_on`, `baz_id`, `tag_id`);
  
  CREATE INDEX `k_account_corge_id` ON `my_facts` (`account_id`, `asked_on`, `corge_id`, `tag_id`);
  
  CREATE INDEX `k_account_grault_id` ON `my_facts` (`account_id`, `asked_on`, `grault_id`, `tag_id`);
  </code></pre>
  
  <p>My problem is that each index takes longer to create than the last -- and it seems to be on a geometric trajectory. In order, the indexes take 11.6s, 28.8s, 44.4s, 76s, and 128s to create. And I'd like to add a few more indexes.</p>
  
  <p>When I create the table as MyISAM, not only is the whole process a whole lot faster, creating each subsequent index takes maybe a second longer than the previous index.</p>
  
  <p>What gives? Is this behavior expected? Am I doing something funny in my index creation?</p>
  
  <p>For what it's worth, I'm using MySQL 5.1.48/OS X 10.6.8 in this test.</p>