Jump to content



Welcome to AstaHost - Dear Guest , Please Register here to get Your own website. - Ask a Question / Express Opinion / Reply w/o Sign-Up!

Toggle shoutbox Shoutbox Open the Shoutbox in a popup

@  yordan : (14 April 2014 - 05:28 PM) By The Way, This Could Be An Interesting Subject For A Topic, What About Posting This Question? Let's See If Other People Have The Same Feeling Concerning Bootlists!
@  yordan : (13 April 2014 - 09:36 AM) Boot Order : Cd, [Usb,] Hard Drive :D
@  yordan : (11 April 2014 - 07:23 PM) I Simply Let The Bios Do That
@  Ritesh : (11 April 2014 - 10:23 AM) Is It Possible To Launch Fedora Live Cd Or Installation Disk From Hard Drive On Windows Platform Using Grub Mbr File.
@  Ritesh : (11 April 2014 - 10:21 AM) No U Are Not.. Btw.. I Have Question For You.
@  yordan : (10 April 2014 - 08:02 AM) You Are Partially Right.
I Was Not.
Nevertheless, I Am Again :)
@  Ritesh : (09 April 2014 - 07:33 PM) :P
@  Ritesh : (09 April 2014 - 07:33 PM) I Think U R Not..
@  yordan : (09 April 2014 - 09:28 AM) I'm The Master Of The Shoutbox!
@  yordan : (05 April 2014 - 10:32 PM) He-He
@  Ritesh : (04 April 2014 - 06:59 PM) Ha Ha Ha ....
@  yordan : (04 April 2014 - 11:15 AM) Welcome Back, Starscream!
@  yordan : (03 April 2014 - 02:31 PM) And I Hope That He Will Come Back Soon :)
@  yordan : (01 April 2014 - 02:53 PM) Nice, Ritesh Came, I'm Not Home Alone Today.
@  Ritesh : (01 April 2014 - 08:51 AM) Oh!!! Poor Dear Yordan..
@  yordan : (31 March 2014 - 10:02 AM) I'm A Poor Lonesome Cow-Boy
@  yordan : (27 March 2014 - 02:22 PM) He Is Unpatient Due To His Patients!
@  Ritesh : (27 March 2014 - 10:46 AM) :(
@  Ritesh : (27 March 2014 - 10:46 AM) He Is Busy With His Patients.
@  yordan : (26 March 2014 - 08:12 PM) Ahsani, Where Are You?

Photo
- - - - -

Mysql Overhead


3 replies to this topic

#1 khalilov

khalilov

    Premium Member

  • [HOSTED]
  • 287 posts
  • Gender:Male
  • Location:Atlantis
  • myCENTs:8.08

Posted 04 October 2008 - 01:55 PM

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 =)

#2 faulty.lee

faulty.lee

    Super Member

  • [HOSTED]
  • 500 posts
  • Interests:Electronics, Software Programming, Embedded Programming, Movies, Windows Shopping
  • myCENTs:79.88

Posted 04 October 2008 - 02:56 PM

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.

#3 khalilov

khalilov

    Premium Member

  • [HOSTED]
  • 287 posts
  • Gender:Male
  • Location:Atlantis
  • myCENTs:8.08

Posted 04 October 2008 - 05:36 PM

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

#4 TavoxPeru

TavoxPeru

    Super Member

  • [HOSTED]
  • 876 posts
  • Gender:Male
  • Location:Lima - Peru
  • Interests:Web and Software development, Internet, Computers, Electronic music, music, soccer.
  • myCENTs:13.21

Posted 08 October 2008 - 05:27 AM

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,



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users