Mysql Overhead

[khalilov 0]

Sometimes in a table especially if i do alot of things in it i start seeing overhead then a size near it? what is overhead? and is it essential to optimize the table to fix it? I know its just a press of a button but i'd like to know what happens when i optimize a table.Also when you make a new table you can limit the size of VARCHAR to a number, that number would be the number characters allowed in that column per entry. I'd like to know how the limit works for texts, is it in KB? or number of characters? Cuz i made a messaging system. And i remember reading somewhere here that each text takes 65kb, thats too much for a simple message in a game. 500 MB is a lot of space, but that doesnt mean it has to be wasted =)

[faulty.lee 0]

Sometimes in a table especially if i do alot of things in it i start seeing overhead then a size near it? what is overhead? and is it essential to optimize the table to fix it? I know its just a press of a button but i'd like to know what happens when i optimize a table.
Also when you make a new table you can limit the size of VARCHAR to a number, that number would be the number characters allowed in that column per entry. I'd like to know how the limit works for texts, is it in KB? or number of characters? Cuz i made a messaging system. And i remember reading somewhere here that each text takes 65kb, thats too much for a simple message in a game. 500 MB is a lot of space, but that doesnt mean it has to be wasted =)

If you set TEXT as your data type for that column, it allows you to store up to 65535 character, which is 64KBytes (1K = 1024), if everything is in ASCII. If you're storing unicode or UTFX(8,16,32), it could be more than that, up to 3 bytes per character. Where as VARCHAR allows a maximum of 255 characters, or less if you specified the limit. If you try to update or insert text longer that the limit you specified, then it will return an error.

These are maximum length of text you can store, but it doesn't mean it will take up that much storage if you use only partially. As mentioned in MySQL's documentation, VARCHAR as an overhead of 1 byte, where as TEXT has 2 bytes. So if you store 10 ASCII character in TEXT, it will take up 12 bytes altogether, not 64KB.

[khalilov 0]

oh in that case i'll keep it as text, is it necessary to optimize a table when i get overhead?

[TavoxPeru 0]

If you have made many changes to a table the data file starts to get fragmented and unused space begins to appear, or if you have deleted some records of a table subsequent INSERT operations reuse this old record positions because the deleted records are mantained in a linked list, so, when this happens you should use the OPTIMIZE TABLE command to reclaim this unused space and to defragment the data file.The OPTIMIZE TABLE command repairs the table if it has deleted or split rows, sort the index pages if them are not sorted, and update the table's statistics if them are not up to date.According to the MySql documentation you need to run the OPTIMIZE TABLE command once a week or month only if it is necesary.My recomendation is to use and run the OPTIMIZE TABLE command every time is needed because it is better to have your tables up to date and in a healthy condition.So, my answer is yes, it is necessary to optimize your table.Best regards,