Optimize tables?


I am newb to MySQL. I was wondering what overhead is and also what optimizing the tables does??



Overhead is created when you edit or delete data in a table, and it is basically “gaps” in the database file where data used to be. You can optimize the table to get rid of the gaps - a process similar to defragmenting your hard drive.

It should be noted that table optimization is only really necessary on huge tables, or tables that are edited and accessed extremely aggressively. Unless you are doing something at the enterprise level, it is unlikely you will get much of a performance gain. I like to optimize my tables once a year, and that is probably more often than necessary.

If you work with binary data, you might prefer to optimize more often, because you are talking about much larger chunks of information.

Simon Jessey
Keystone Websites | si-blog


Thanks for the info!


While you are on the subject of optimization, does it also recalculate the auto increment fields? For example if several records are deleted and a new record is added, there is a gap in numbers. So records 1-10 with record 9 and 10 deleted, the next record is 11. I hope I am explaining this good enough.


Noooo! It leaves auto increment fields untouched (thank goodness). So yes, if you are missing a number 9 in a list of 1 through 10, your next auto increment number is still going to be 11.

Simon Jessey
Keystone Websites | si-blog


Ok thanks.