Geek Answers Handbook

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

This may be a bit like asking how long the string is, but statistics:

  • Intel Dual-Core 4GB RAM
  • A table with 8 million rows, ~ 20 columns, mostly varchars with the main identifier auto_increment
  • Request: ALTER TABLE my_table ADD INDEX my_index (my_column);
  • my_column - 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 minutes on a lower specialized machine with almost identical settings. I have no idea why it took so much longer. The only possibility is that the prod HD machine is 85% full without using 100 GB. It should be enough, but I think it depends on how this free space is distributed.

+7
mysql indexing
source share
3 answers

If you just add a single index, it will take about 10 minutes. However, it will take 100 minutes or more if you do not have this index file in memory.

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

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

 myisam_sort_buffer_size = 100M sort_buffer_size = 100M

Good luck.

+5
source share

In addition, if you ever need to create multiple indexes, it is best to create all indexes in one call, and not individually ... Reason: basically it tries to rewrite all index pages by including a new index in it, with any other it was . In the past, I found out about this with a 2g gig table, and he needed to build about 15 indexes. The construction of each individually constantly increased in time between each index. Then the attempt at once was a little more than about 3 separate indexes, because she built all the records and wrote everything right away, instead of continuing to rebuild the pages.

+1
source share

In my MusicBrainz test base, the track table creates 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 entries.

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

@@myisam_sort_buffer_size 256M .

0
source share

More articles:


All Articles