As you might know ANALYZE TABLE just quickly updates table statistics using index dives, unlike with MyISAM when it scans indexes holding table lock for long period of time.

So ANALYZE TABLE should be very fast and non intrusive operation doing just little update on the data. Right ?

Wrong! There is the bug or rather MySQL Design Feature which causes ANALYZE TABLE to block all accesses to this table while it could be flushed from the table cache.

What does this mean in practice ? If you have some long running query accessing Innodb table and you run ANALYZE TABLE you will be unable to access that table with “Waiting for table” lock until the first query completes.

For applications which run short transactions it may not be the big deal but if you mix long reporting together with real time update queries this can be the real issue.

This is generally the type of gotcha I hate the most. From the glance view there are no reasons Innodb can’t do this without locks but in practice some old mysql design artifacts result in this behavior and the bug can’t be fixed quickly.

Newest Most Voted
Inline Feedbacks
View all comments
Brian Aker

How useful is Analyze with Innodb?

Rob Wultsch

Does ANALYZE on Innodb initialize the auto-increment counter?

That page appears to say no, but it seem to me that something that would make sense to pull stats on when an ANALYZE is run. Any thoughts?


Peter, from my experience ANALYZE TABLE doesn’t do an exact count — at the very least, the stats are still approximate after an ANALYZE TABLE. That being said, it can be useful, but not as useful as some people might think.


When you get down to it, unless you have reason to believe your data changed “shape” sinificantly, there’s not usually an advantage to running stats in my experience (on innodb or any other db for that matter).

Main thing the optimizers are looking for are cardinality values on your indexes and skewing. If neither of those changes appreciably, then neither will your plans and in the meantime you’ll have burned up some set of server resources (and a lock) gathering stats.


Can you modify or delete the statistics after analyze table was running? (I only use Innodb.)
Would be an appropriate method of taking influence on execution plan. (I’ve done several times in Oracle.)


is it ok to kill the analyze query when it is running state??? pls guide urgnt


Hi Peter,

Seems it is very old article ford mysql versions.
We just tried analyzing the table which is getting used in delete statement from past 2 hours on mysql5.6 and it analyzed without waiting for lock or something else.