CODEWITHSUNDEEP
mysqlsql
user2071938
user2071938

Reputation: 2255

Mysql size of tables is not immediately updated after delete

I have a database which holds Logentries of several applications. Now I have written a bash-Script which should delete the oldest day when a size limit is exceeded. I have a loop which deletes day per day until the acctual size is smaller then the limit. But after the delete-statements the size of the table is not properly updated.

I used this Sql Statement

'SELECT round(((data_length + index_length)), 0) "Size in Bytes" FROM information_schema.TABLES WHERE table_schema = "Log" AND table_name = "Log";'

to determine the acctual table size. How can I force MySql to recaclulate this size immediately after a delete command?

Upvotes: 26

Views: 21674

Answers (2)

Pedro Bonilla
Pedro Bonilla

Reputation: 451

ANALYZE TABLE tableName;

This is also possible for refreshing the table statistics.

Ref: https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html

Upvotes: 35

Benvorth
Benvorth

Reputation: 7722

Run

 OPTIMIZE TABLE Log

After deleting. This will update the index statistics (and free all unused disk-space btw).

Upvotes: 34

Related Questions