Re-architecting MySQL Indexes

I have to re-architect the index structure because of previously bad index design. I have some 200 tables with a few thousand up to 20 million rows per table. It would take days to manually remove indexes and create new indexes on the life system.

So here is my thought:

1. I erase the schema (after 开发者_StackOverflow社区backup ;)

2. Create a new schema

3. Run a complete series with CREATE TABLE statements declaring the needed Primary Keys and indexes (takes a minute or so)

4. Run a RESTORE of the DB into that new schema (This may take 2 hours - but not days)

Does that make sense?

---

I think you're proposed solution is similar, but you could start up a new replication slave and then make the changes to the schema on that machine first. As long as you are only making changes to indexes then events will continue to be re-applied with no errors.

Once the changes have completed you could then 1) promote slave to master, apply same changes on slave and then switch back or 2) stop both MySQL instances and copy across the raw data files from the slave to the master.

Using this method, the indexes are still rebuilt but it does really matter how long it takes to make the changes on the slave. The only downtime is length of time it takes to copy the files across.

There are other solutions out there that can perform non-blocking changes to tables without use of a slave such as oak-online-alter-table http://openarkkit.googlecode.com/svn/trunk/openarkkit/doc/html/oak-online-alter-table.html and a similar tool developed by Facebook http://www.facebook.com/notes/mysql-at-facebook/online-schema-change-for-mysql/430801045932.