CODEWITHSUNDEEP
mysqlsql
Leo Jiang
Leo Jiang

Reputation: 26075

Increasing varchar length decreased size in MySQL?

I have a table with ~100000 rows. It's 62.6 MB and it has no overhead. I changed 2 columns from varchar(255) to varchar(2047). The columns are for URLs. After the change, the table still has the same number of rows, but the size decreased to 59.2 MB.

I'm very confused as to why this happened. Shouldn't varchar(2047) be either the same size or greater? Can someone explain why an increase in varchar length led to a smaller size?

Upvotes: 0

Views: 723

Answers (1)

invisal
invisal

Reputation: 11171

VARCHAR and CHAR Differences

  • CHAR length is fixed. For example: CHAR(30) means it will store fixed 30 characters, even you insert a value with 10 characters, it will try to add some padding.

enter image descriptionhere

  • VARCHAR is variable size length. VARCHAR(300) means it will store at most 300 characters. The database does not reserved 300 bytes for it. It will store only the length that you have inputted.

enter image description here

  • When you update new value with the greater length, it will use overflow pointer.

enter image description here

Decreased Size

I do not know the exactly cause, but this is what I believe.

  • When you delete a record, MySQL does not truly delete. It simply mark the record as deleted and will reuse when new data is inserted.
  • The deleted record is maintained by Linked List (for variable-sized record) so that it can quickly find the deleted record for reused.

enter image description here

  • That's mean when you delete your data, the size of your database is still the same until you run OPTIMIZE TABLE your_table_name to re-organize your database again.
  • What I believe that cause the size to decrease is when you change the table schema, the database will try to re-organize your database again so that there is no more deleted data or used space.

Upvotes: 1

Related Questions