We have tables with over 10 or 20 millions of records (MyISAM tables if that matters).
Building new index, removing old index is taking at least a quarter of hour.
While doing this operation, MySQL seems to lock the table or at least the load is so big that any operations on the table is delayed after the end of index operation.
I wonder how DBA usually deal with this kind of problem on MySQL ?
I cannot imagine a strategy which would help keeping the service a hundred per cent available while achieving an index reconfiguration.
I guess there’s no real solution for that problem. We’re having the same problem on our database (also 10/20 millions records per table).
If you can afford to stop your service while you’re generating the indexes, you should try stopping the server, change my.cnf’s parameters for best memory usage on creating indexes, alter the index, and than undo your change and start mysql again. I’m not sure what would be the best parameters to speed up index generation in mysql, but I guess you should try to increase myisam_sort_buffer_size?