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

Question

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.