There comes a point when a step-by-step clean-up becomes more work than a clean slate and migrate approach. System availability and time to migrate may factor in to the decision when dealing with larger volumes but at this size, not an issue.

Key factors for me here are:

• Renaming foreign key constraints to fit a new application framework.
• Refactoring a significant proportion of existing tables.
• Low volume of data.

In this situation I'd be very tempted to design a new schema that fits the model you now require and create the necessary scripts to migrate data across (your option 4).

First thing to know is what ANALYZE TABLE does.

For a MyISAM table mydb.mytable, ANALYZE TABLE traverses all the indexes in the mytable.MYI file, reads all BTREE pages, computes statistics, and stores in them in the information_schema, specifically STATISTICS. You can see it when you run one of the following:

• SHOW INDEXES FROM mydb.mytable;
• SELECT * FROM information_schema.statistics WHERE table_schema='mydb' AND table_name='mytable';

ANALYZE TABLE is a read-only operation and does issue a table-wide read lock.

ANSWER TO YOUR QUESTION

Yes, you can kill ANALYZE TABLE and wait briefly for the read lock to be released. However, there is a significant penalty. Because the index statistics will not be completely computed, the MySQL Query Optimizer will take rather bad guesses at generating EXPLAIN plans of queries against that table.

SUGGESTIONS

SUGGESTION #1 : Delay ANALYZE TABLE

If you want to run OPTIMIZE TABLE mydb.mytable without doing the ANALYZE TABLE operation, do the following:

ALTER TABLE mydb.mytable RENAME mydb.mytable_opt;
ALTER TABLE mydb.mytable_opt ENGINE=MyISAM;
ALTER TABLE mydb.mytable_opt RENAME mydb.mytable;

This will rename the table, perform the defragmentation, and rename it back.

You could do it in one line as well

ALTER TABLE mydb.mytable ENGINE=MyISAM;

If you want to run ANALYZE TABLE later on, set up a cron job to run it in every midnight or perhaps once a week. You will have to do this inevitably if you expect queries against your table to perform well after an optimization.

SUGGESTION #2 : Change the MyISAM table to InnoDB

During the generation of an EXPLAIN plan, InnoDB will perform reads against the index pages of a table by traversing 2-3 levels of nonleaf nodes and literally approximate (or really guess) the cardinality of all column levels. You can tell this is happening to an InnoDB table when you run SHOW INDEXES FROM mydb.mytable; because the cardinality changes with each run.

In light of this, you can simply run

ALTER TABLE mydb.mytable ENGINE=InnoDB;

and that's it. Even if you ran ANALYZE TABLE on an InnoDB table, the statistics can get rather stale. By default, innodb_stats_on_metadata is already disabled in MySQL 5.6. If you have MySQL 5.5 and prior, you can disable the update of the statistics by doing this:

SET GLOBAL innodb_stats_on_metadata = 0;

You will still have to bite the bullet and run ANALYZE TABLE sometime downstream if the InnoDB table experiences high INSERTs, UPDATEs, and DELETEs. Otherwise, the MySQL Query Optimizer will take bad guesses at EXPLAIN plans.