CODEWITHSUNDEEP
mysqlindexing
Richard H
Richard H

Reputation: 39095

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

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

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.

Upvotes: 11

Views: 32863

Answers (3)

Quassnoi
Quassnoi

Reputation: 425603

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.

Upvotes: 2

DRapp
DRapp

Reputation: 48149

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.

Upvotes: 1

vy32
vy32

Reputation: 29667

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.

Upvotes: 8

Related Questions