CODEWITHSUNDEEP
mysqlsql
DarkFenix
DarkFenix

Reputation: 746

Does decreasing the size of varchar not minimize disk space? MySQL

I am optimizing a database and I have noticed that the size of my column of type varchar (255) is very large, which generates disk space, where my values will not exceed 30 characters.

ALTER TABLE tablename MODIFY COLUMN columnname VARCHAR(30);

I changed the size of the column but the total in mb does not decrease (before it was 35.mb, now it remains the same), what can I do?

How can I decrease the storage or will that change be only for future inserts?

 SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) size_mb 
    FROM information_schema.TABLES 
    WHERE table_schema = "db"
    ORDER BY size_mb desc;

Upvotes: 1

Views: 1320

Answers (2)

Rick James
Rick James

Reputation: 142540

Your change may actually speed up some queries.

In a complex SELECT, a temp table may need to be constructed. Whether this temp table can be held in RAM or needs to be instantiated on disk depends on several factors. One of the factors is the max size of each VARCHAR.

Note also, your 255 (now 30) is characters, not bytes. Needing a 2-byte prefix depends on bytes. Hence, if you are using utf8, 255 forces a 2-byte prefix, but 30 does not.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271151

No, changing the declared length of a varchar column does not really change the amount of storage needed.

The length of a varchar value is described as:

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value. A column uses one length byte if values require no more than 255 bytes, two length bytes if values may require more than 255 bytes.

In other words, changing the declared length of a varchar() value could only change the size of the data you changed the value from over 255 to under 255. You are not doing that.

You would save space if values were truncated. However, MySQL does allow that; it returns an error instead (see an example here).

You will have to look elsewhere to save space. Note that 35Mb is not particularly large for a database table.

Upvotes: 3

Related Questions