CODEWITHSUNDEEP
mysqlinnodb
Nate
Nate

Reputation: 4747

MySQL: Each index on InnoDB table takes longer to create than the last

I've got a MySQL table that looks like this:

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;

and has 450k rows. But: I want to add several indexes to it:

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`);

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.

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.

What gives? Is this behavior expected? Am I doing something funny in my index creation?

For what it's worth, I'm using MySQL 5.1.48/OS X 10.6.8 in this test.

Upvotes: 3

Views: 1646

Answers (1)

Dan Grossman
Dan Grossman

Reputation: 52372

This is expected behavior based on how index creation happens in InnoDB.

In MySQL versions up to 5.0, adding or dropping an index on a table with existing data can be very slow if the table has many rows. The CREATE INDEX and DROP INDEX commands work by creating a new, empty table defined with the requested set of indexes. It then copies the existing rows to the new table one-by-one, updating the indexes as it goes. Inserting entries into the indexes in this fashion, where the key values are not sorted, requires random access to the index nodes, and is far from optimal. After all rows from the original table are copied, the old table is dropped and the copy is renamed with the name of the original table.

Beginning with version 5.1, MySQL allows a storage engine to create or drop indexes without copying the contents of the entire table. The standard built-in InnoDB in MySQL version 5.1, however, does not take advantage of this capability.

Upvotes: 4

Related Questions