I have a development database on my server and during testing i find that there is a lot of overhead in one of the tables after a short amount of use.....
Data 884 Bytes
Index: 7,168 Bytes
Overhead: 994 Bytes
Yet all the other tables have no overhead at all, I can't seem to find any clear information on what caused overhead or if it is important.
Maybe some experianced mysql user might enlighten us as to what creates overhead and how to avoid it as much as possible.
Thanks
Contolling overhead in mysql
Moderator: General Moderators
-
malcolmboston
- DevNet Resident
- Posts: 1826
- Joined: Tue Nov 18, 2003 1:09 pm
- Location: Middlesbrough, UK
i currently cannot remember the name of the function (probably the drink to be honest
) but there is a function in PHPmyADMIN that basically, "cleans" up the table and optimises it basically, try that and see if theres any change.
It would also be a good idea to write a function that can optimise your tables automatically, so basically yopu could click a link in your CMS area of your site and it would optimise all your tables for you automatically, i have a tendency to employ this.....
..hope that helps
It would also be a good idea to write a function that can optimise your tables automatically, so basically yopu could click a link in your CMS area of your site and it would optimise all your tables for you automatically, i have a tendency to employ this.....
..hope that helps
Yes I understand that the above 'malcolmboston'is possible but I am really trying to find out the why's behind what causes a tables overhead and why one table needs it and another not. Is it because my php scripts are badly written, is it the way the inserts, updates are being made? what affects this aspect of a table in mysql?
Thanks
Thanks
MySQL uses files to store the data and as they are accessed, an 'overhead' is built up of data which is cached, in use, stored temporarily etc. Over time, this overhead builds up and I would assume you would eventually get a performance decrease. The OPTIMIZE command from within MySQL and phpMyAdmin gets rid of the overhead.
There really isnt any need as far as I can see. The overhead is really small, and will probably go up and down by itself since you have people adding and deleting from the table all the time.
If we were talking many MB I could understand you, but a few hundred KB is really silly spending so much time avoiding since it will appear in 5 minutes again when people delete something from your table.
If we were talking many MB I could understand you, but a few hundred KB is really silly spending so much time avoiding since it will appear in 5 minutes again when people delete something from your table.
I haven't really worried about the overhead before as it has only ever been a very small amount 'if any' on a few tables from other databases I have worked on, much smaller than what is showing here and so my attention was never really drawn to it, or at least it was but not enough to interest me. I felt it strange in this case because the table was so small and only has 1 'text' column apart from the main id column and the id column reference from another table, although the 'text' column entries are updated a lot.
Thanks
Thanks