0

I have a table witch is the core of my system, all the results shown to my customers are stored there. It grows quite fast so each 3 hours I should delete the records older than X in order to improve performance.

Is it enough to only delete those records or should I run Optimize table after the deletes?

I'm considering to delete them each 3 hours and only run Optimize once per day and at night but I'm not sure if just deleting is enough to improve performance.

javier_domenech
  • 5,995
  • 6
  • 37
  • 59
  • If you have an autoincrement id as primary key, I doubt you would see any difference at all (it might depend on other indexes you have and use). You can of course just test it, and as since 5.7.4 `optimize table` won't lock anymore, it wouldn't do much harm either (it will of course still use i/o), so you can go both ways. But I guess in general I would just do it during normal nightly maintainance (without knowing your specific setup of course). – Solarflare Jun 06 '16 at 14:58
  • I would utilize an [event](http://stackoverflow.com/a/32508935) for this. Think of events as scheduled recurring stored procedures, without the hassle of cron. They are set and forget. So there is the downside of forgetting they execute. Keep reminding yourself of that now and then: "Oh I forgot, I have events in place doing that." – Drew Jun 06 '16 at 18:06

1 Answers1

2

If you are using InnoDB, do not bother with OPTIMIZE TABLE; it is not worth the effort.

If you are always deleting the "oldest" rows, then there is a much faster and more efficient way to do it. And it, inherently, needs no OPTIMIZE. Partition the table over date and DROP PARTITION. Each partition will be separate structured; hence dropping one partition does not fragment the rest of the table.

See my blog for further details.

Rick James
  • 135,179
  • 13
  • 127
  • 222