Menu
I truncated several huge tables, but the mysql file and database appears to remain at the same size.
Does truncate table delete the data from mysql as well?
595
TRUNCATE TABLE empties a table completely. It requires the DROP privilege. Logically, TRUNCATE TABLE is similar to a DELETE statement that deletes all rows, or a sequence of DROP TABLE and CREATE TABLE statements. To achieve high performance, TRUNCATE TABLE bypasses the DML method of deleting data.
When a table is truncated, it is dropped and re-created in a new . ibd file, and the freed space is returned to the operating system.
TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.
No, truncate doesn't release anything. The reason why truncate is quick is because it simply moves the High Water Mark in the table, effectively back to the start of the table so that it appears there is no longer any data in it.
When a large table is deleted or truncated InnoDB storage engine for MySQL will leave the empty space, and use it for subsequent insertion of new row versions.
There are ways to shrink database. Check Re: how to shrink a MySQL database
105
In Version 5.1.55 size was not reduced after Truncate , even with innodb_file_per_table='ON'. So need to DROP and then CREATE the table and then Size was reduced.
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
