Overhead question (MySQL)

[ironik]

This is going to sound really lame, but after a couple of years working with MySQL and PHP I still don't understand what an overhead is and how it affects the performance of table...

I have a table that is being constantly accessed and updated and it appears to gain a small overhead (PHPMyAdmin prompts for optimisation often)... is this a problem?

[coopster]

Although I am not familiar with phpMyAdmin, I have worked with databases for quite a few years and will give you my best effort here ...

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.

[ironik]

Brilliant explanation! Understand perfectly now :)