Overhead

[The Little Guy]

Can I get an explanation on this please? I can't find a good one on google. All I know is that OPTIMIZING tables removes the overhead, and I guess that is a good thing.

[LemonInflux]

As you update and delete rows in your database, it builds up overhead.

 

Think of it like using a car. You can use it for so long, then it needs servicing. That's what optimizing is. Overhead is the build up of...well, rubbish, I suppose.

 

That's how I always think of it :/

[The Little Guy]

if you delete it, what builds up?

[PFMaBiSmAd]

When information is deleted or updated with data using smaller amount of storage, this leaves gaps in the table. The database storage engine maintains a table of the empty gaps and attempts to reuse them when new information is added. The "overhead" of finding an unused space of the necessary size and using it takes extra time. By optimizing the table (defragmenting it), the unused gaps are removed, so the storage engine does not spend the extra time trying to fill them with new data. Also, the more entries there are in the table of the empty gaps, the longer it can take to scan that table to find an appropriate entry to reuse.

[The Little Guy]

I have a database that gets changed lots there is an average of 3,449,640 queries/day (mainly SELECT). Would it be a good idea, to have a SQL cron job that optimizes the tables every night for all the tables that have 500K+ overhead?