In MySQL what does "Overhead" mean, what is bad about it, and how to fix it?

Question

simple question, but its been nagging me for a while now....

what is "overhead" in MySQL, and should i be worried?

does clicking "optimize table" fix it for real?

Answer 1

It appears that the overhead is temporary diskspace that the database used to run some of the queries, so you should only worry if this gets really high.

You can compare 'Optimizing the table' with the defragmenting of your hard drive.

I quote:

Every database will, over time, require some form of maintenance to keep it at an optimal performance level. Purging deleted rows, resequencing, compressing, managing index paths, defragmenting, etc. is what is known as OPTIMIZATION in mysql and other terms in other databases. For example, IBM DB2/400 calls it REORGANIZE PHYSICAL FILE MEMBER.

It's kind of like changing the oil in your car or getting a tune-up. You may think you really don't have to, but by doing so your car runs much better, you get better gas mileage, etc. A car that gets lots of mileage requires tune-ups more often. A database that gets heavy use requires the same. If you are doing a lot of UPDATE and/or DELETE operations, and especially if your tables have variable length columns (VARCHAR, TEXT, etc), you need to keep 'er tuned up.

Answer 2

If you are talking about the thing that phpMyAdmin calls overhead, then it's actual size of a table datafile relative to the ideal size of the same datafile (as if when just restored from backup).

For performance reasons, MySQL does not compact the datafiles after it deletes or updates rows.

This overhead is bad for table scan, i. e. when your query needs to run over all table values, it will need to look at more empty space.

You may get rid of the overhead by running OPTIMIZE TABLE that will compact your table and rebuild its indices balancing them.

Answer 3

I tried to optimize my innoDB table. but it says "Table does not support optimize, doing recreate + ...." and the overhead is still there...
any other idea?