Adding an index very slow...is there a mysql cmd to get an ETA or show progress?

I'm currently running an alter query on my table (20M entries)
in order to add an index. it's running for more than 3 days already
(stuck on 'copy to tmp table').

is there a way I can see the progress of the query or in other word
is there a way I can get an estimation time of completion ?

thanks.

When mysql alters a table, it essentially makes a copy of of it and then swaps the copy in. This way if you cancel the update in the middle, the table is still in a stable state. So you can look in the mysql data directory (/var/lib/mysql/ ?) to see how large the new file is, that will tell you how far along it is. This is a bit more difficult with Innodb, but there is a tmp table being created somewhere.

You can significantly reduce the amount of a time an index takes by increasing your sort buffer variables (myisam_sort_buffer_size, sort_buffer_size). Make those as large as you can with memory you have. You could cut a few days off of the modification time, even get it down to a few hours depending on how much memory you have. I've done a 150M record table in about 3 hours.

StackExchange Database Administrators Q#10732, answer score: 4