CODEWITHSUNDEEP
mysqlindexing
Jason Swett
Jason Swett

Reputation: 45124

Adding index on large table takes forever

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?

Upvotes: 21

Views: 13196

Answers (1)

ircmaxell
ircmaxell

Reputation: 165251

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...

Upvotes: 21

Related Questions