1

I've got a couple of tables that get cleared out regularly by a cron job on a web server. However when these tables are completely empty it looks like there is still data in them.

e.g. This database is showing 2 tables that have 0 rows, yet their size is not 0. If i truncate the tables they do go down to 0.

The only additional data I can think of that might be contained in these tables are the auto increment number of the id fields, but considering the number in the first table is only at 29 as of the screenshot 117Kb seems quite a lot for such a small bit of data.

empty tables...

Sorry if this is an incredibly obvious answer, I don't know a huge amount about how databases actually work.

Novocaine
  • 117
  • 2
  • 11
  • What do you mean by "cleared out regularly"? Deletes or truncates? (I don't know MyISAM specifically, but db engines generally don't release physical storage after a delete.) – Mat Sep 08 '14 at 15:44
  • It would be just deletes in this case. – Novocaine Sep 08 '14 at 15:56

1 Answers1

2

TRUNCATE effectively drops and re-creates the table, so the allocated space is gone. A DELETE... command will only remove the rows but does not cause the table size on disk to automatically shrink. This would cause excessive I/O for every DELETE... command to shrink files if it were true.

Truncate Reference for MySQL 5.7

LowlyDBA - John M
  • 10,922
  • 11
  • 42
  • 62
  • Should I then add to my script to perform a truncate if there are no more rows remaining to keep things clean, or does this not really matter too much. This table will never become large, not even in the hundreds. – Novocaine Sep 08 '14 at 15:57
  • 1
    TRUNCATE is always preferable over DELETE... when you wish to remove all rows from a table. – LowlyDBA - John M Sep 08 '14 at 16:00
  • 1
    @Novocaine: if the table won't become very large, why are you worried about deleting a few 100k repeatedly just to see them come back again? Seems like a waste of time/resources. – Mat Sep 08 '14 at 16:06
  • @Mat OCD probably ;) – Novocaine Sep 08 '14 at 16:08