How to apply an index to a MySQL / MyISAM table without locking?

Question

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?

Answer 1

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.