First thing to know is what
ANALYZE TABLE does.
For a MyISAM table
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.
SUGGESTION #1 : Delay
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.