Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm trying to optimize a table that hasn't been optimized in a long time. It has around 2.5 million rows with 1.3 GB of data and a 152 MB index. I started optimizing it about 15 minutes ago and I have no idea how long it will take.

The server is reasonably robust (quad xeon with 4GB ram) and has a 500MB innodb buffer pool size.

Should I expect this to take minutes, hours or days?

share|improve this question

It depends a lot on what the status of the data in the table is and your hardware, but a typical upper bound on innodb alter table operations is the time needed to copy the whole table + the time needed to sort the index.

Have you made sure you are not running out of disk space?

share|improve this answer

The answer, apparently, was 1 hour, 20 minutes. I'm not sure if that's a "normal" time or if it was delayed by some inefficiency in my setup.

share|improve this answer
Seems slow to me. What's the disk config? – James Apr 10 '10 at 0:02

I am not yet sure why, but sometimes such operations take hours. I witnessed a 2-hour-long run and heard about an 8-hour-long one. To alter a table, MySQL has not only to copy it to a new one, but also to re-index it. This is where it usually spends most of the time. If you check PROCESSLIST, it will most probably say it's creating index or sorting index.

It's not normal, but it happens, unfortunately.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.