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.
