Performance Issues of TRUNCATE/DROP TABLE in MySQL and Their Historical Fixes

The performance degradation caused by TRUNCATE and DROP TABLE operations in MySQL has been a cross‑version issue. By following the official manuals and historical bug reports, we can chronologically outline the symptoms and the official optimizations.

Guided by the official manual : MySQL 5.7 documentation notes that on systems with a large InnoDB buffer pool and innodb_adaptive_hash_index enabled, removing adaptive hash index entries during table deletion triggers an LRU scan, temporarily lowering performance. MySQL 5.5.23 fixed the DROP TABLE problem (bugs #13704145, #64284), but TRUNCATE TABLE remained a known issue (bug #68184).

MySQL 8.0’s manual states that the previous version’s TRUNCATE performance problem is solved by mapping TRUNCATE to DROP TABLE + CREATE TABLE .

Historical bugs as clues :

Bug #64284 & #51325: DROP TABLE performed two LRU scans—one for data pages, one for AHI entries. The fix reduced it to a single LRU scan for AHI and a flush‑list scan for data pages, with periodic release of the buffer‑pool mutex.

Bug #61188: For partitioned tables, each partition’s drop repeats the double LRU scan, amplifying the issue.

Bug #68184: TRUNCATE TABLE scans LRU to delete AHI; fixed in 8.0 by mapping to DROP TABLE + CREATE TABLE .

Bug #91977: DROP TABLE still blocks due to LRU‑based AHI deletion.

Bug #98869: Temporary InnoDB tables created by queries still suffered the same issue until it was fixed in 8.0.23.

MySQL 5.5.23 lazy‑deletion strategy : The optimization replaced the second LRU scan with a flush‑list scan for data pages, allowing LRU‑list pages to expire lazily, and made the buffer‑pool mutex release periodically.

MySQL 5.7 improvements : Drop‑table now avoids scanning the LRU list for index pages by checking the on‑disk tablespace; data pages are still removed via a flush‑list scan, while LRU pages expire lazily. The benefit is significant for small tables but limited for large ones.

MySQL 8.0 changes : TRUNCATE is mapped to DROP TABLE + CREATE TABLE , but AHI deletion still requires an LRU scan, reverting to the cost seen in 5.5.23 for small tables.

MySQL 8.0.23 release note : Data‑page and AHI deletions become asynchronous (lazy), yet the official blog indicates the AHI change is not yet fully implemented.

MySQL 8.4 : The default for innodb_adaptive_hash_index is now OFF , effectively disabling AHI unless explicitly enabled after performance testing.

In practice, if frequent TRUNCATE/DROP TABLE operations cause performance spikes or crashes, upgrading to MySQL 8.0.23+ helps, and disabling AHI is a viable mitigation when the issue persists.

Thank you for reading.