Adding index on large table takes forever

Question

I have a table (in MySQL) called unused with about 5.4 million rows. The table looks like this:

CREATE TABLE `unused` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account_id` bigint(20) DEFAULT NULL,
  `heading_label` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `fk_account_id` (`account_id`),
  CONSTRAINT `unused_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `account` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=80524905 DEFAULT CHARSET=latin1

I thought queries on this table might go faster if I were to add an index. I tried running this:

create index heading_label
on unused (heading_label) using btree

I let this command run for maybe an hour or two before restarting MySQL. Even though this table has over 5 million rows, it doesn't seem like it should take over an hour to run this sort of thing. But maybe that's normal. I don't really know what I'm doing. Can someone enlighten me?

Answer 1

It's normal depending on your server specs. The way MySQL creates indexes is by the table and then sorting and adding the indexes. This means that it needs to re-write all the data, and then sort all of the data (not cheap by any means). It depends on your server's I/O performance and how much ram you can give it.

Here are a few resources for more information...

• A MySQL bug report
• Another MySQL bug report - with a good suggestion
• A forum thread with some useful info