2

When I change the content of VARCHAR(32) from 32 chars to 0, will it save some space?

The reason I'm asking it is because I store a hash of 32 chars to check something but after a while I'll not use it anymore, so if it takes some of DB space, I might change this field to empty ''.

According to the manual, it takes 1b plus the chars for strings with 255 chars or less, but what if we already have some data and change it to empty, does it save some space?

I'm currently using MySQL with InnoDB engine.

PS.: I'm a beginner yet, please bare with me.

  • found similar question stackoverflow.com/questions/21555485/… – Ahmad Abuhasna Aug 17 '16 at 12:52
  • @AhmadAbuhasna, thanks but it's talking about wiping all the data from that column, my point here is to empty the fields of the rows that I'll not use anymore, but some other rows will use it, so if I set VARCHAR(0), it can only contain null or empty string in all rows. – William Aug 17 '16 at 12:57
3

You are leaving the datatype at VARCHAR(32), correct? But updating the value to be the empty string, correct?

A 32-char hash takes 33 bytes -- 1 byte length plus 32 hex characters (1 byte per character).

The empty string will take 1 byte -- 1 byte length, plus no space for any characters.

However... You are deleting stuff out of the middle of blocks in the middle of the table. So don't expect that zapping a million hashes will necessarily shrink the table by 32MB. There are too many things going on with blocking, free space, reuse of free space, etc, to say anything more than "it will free up some space for future usage".

I don't know where you got the 1KB info in that link -- the length is 1 byte, not 1 kilobyte.

  • Thank you Rick for the explanation. You are right I miss typed 1kb, I'll edit the question to not confuse anyone else. Cheers! – William Aug 18 '16 at 10:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.