tags:

views:

441

answers:

1

Having a production table, with a very critical column (date) missing an index, are there any ways to apply said index without user impact?

The table currently gets around 5-10 inserts every second, so full table locking is out; redirecting those inserts to alternative table / database, even temporarily, is also denied (for corporate politics reasons). Any other ways?

+2  A: 

As far as I know this is not possible with MyISAM. With 5-10 INSERTs per second you should consider InnoDB anyways, unless you're not reading that much.

Are you using replication, preferable in a Master-Master Setup? (You should!) If that is the case, you could CREATE INDEX on the standby server, switch roles and do the same, then switch back. Be sure to disable replication temporarily (when using master-master) to avoid replicating the CREATE INDEX to the active node.

Depending on whether you use that table primarily to archive Logs or similar, you might aswell look into the Archive Storage engine.

Jan Jungnickel
Definitely move to InnoDB - one of these days that MyISAM table is going to get corrupted and then you'll have real political problems. Anyway, this answer is correct. If you don't have replication running, tell the politicians that you need it - you do.
Adam Nelson