How long should it take to build an index using ALTER TABLE in MySQL?

Question

Hi All,

This might be a bit like asking how long a length of string is, but the stats are:

• Intel dual core 4GB RAM
• Table with 8million rows, ~ 20 columns, mostly varchars with an auto_increment primary id
• Query is: ALTER TABLE my_table ADD INDEX my_index (my_column);
• my_column is varchar(200)
• Storage is MyISAM

Order of magnitude, should it be 1 minute, 10 minutes, 100 minutes?

Thanks

Edit: OK it took 2 hours 37 minutes, compared to 0 hours 33 mins on a lesser spec machine, with essentially identical set ups. I've no idea why it took so much longer. The only possibility is that the prod machine HD is 85% full, with 100GB free. Should be enough, but i guess it depends on how that free space is distributed.

Answer 1

If you are just adding the single index, it should take about 10 minutes. However, it will take 100 minutes or more if you don't have that index file in memory.

Your 200 varchar with 8 million rows will take a maximum of 1.6GB, but with all of the indexing overhead it will take about 2-3 GB. But it will take less if most of the rows are less than 200 characters. (You might want to do a select sum(length(my_column)) to see how much space is required.)

You want to edit your /etc/mysql/my.cnf file. Play with these settings;

myisam_sort_buffer_size = 100M
sort_buffer_size = 100M

Good luck.

Answer 2

Additionally, if you ever need to build multiple indexes, its best to create all indexes in one call instead of individually... Reason: it basically apears to rewrite all the index pages to be inclusive of your new index with whatever else it had. I found this out in the past having a 2+ gig table and needed to build about 15 indexes on it. Building all individually kept incrementally growing in time between every index. Then trying all at once was a little more than about 3 individual indexes since it built all per record and wrote all at once instead of having to keep rebuilding pages.

Answer 3

On my test MusicBrainz database, table track builds a PRIMARY KEY and three secondary indexes in 25 minutes:

CREATE TABLE `track` (
  `id` int(11) NOT NULL,
  `artist` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `gid` char(36) NOT NULL,
  `length` int(11) DEFAULT '0',
  `year` int(11) DEFAULT '0',
  `modpending` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `gid` (`gid`),
  KEY `artist` (`artist`),
  KEY `name` (`name`)
) DEFAULT CHARSET=utf8

The table has 9001870 records.

Machine is Intel(R) Core(TM)2 CPU 6400 @ 2.13GHz with 2Gb RAM, Fedora Core 12, MySQL 5.1.42.

@@myisam_sort_buffer_size is 256M.