Freitag, 6. Juni 2014

ALTER TABLE ADD INDEX: What can go wrong?

Answer: EVERYTHING!!
(@Groves really everything)

So, i just found out that doing an alter table to add an index without an maintenance is the worst decision you can make.

What happens,

  1. You fire the alter command
  2. InnoDB will alter its own engine, before altering the table itself
  3. all queries against the database will complain about an index issue
  4. All queries (alter and select) will  go to state : "Waiting for table metadata lock"
lesson learned!