Need help in MySQL DB Design (minimizing the overhead)

Question

I am trying to design a mysql DB but dunno what to do to minimize the overhead to a minimum, I have uniq needs. one of the fields could be as long as 60kb or short as 100 bytes, what kind and lenghth should I use in this field to minimize the overhead to its minimum ?

I've heard if you define the maximum to be like 60k then every unused space left in the row till 60k will be filled with space, you understand that this could cause unnecessary overhead, only few raws would make use of this lenghth while most of the rest rows won't, what do you suggest ??

Reserving unused space only happens on `CHAR` columns. You should be fine with any of the other types (`VARCHAR`,`TEXT`...). — Vatev, Feb 13 '13 at 12:22
Do you have a real world problem that you are trying to solve here? If you don't, don't worry about space usage at this stage. — Pekka, Feb 13 '13 at 13:37

score 2 · Answer 1 · answered Feb 13 '13 at 19:12

2

In MySQL, VARCHAR stores compactly on disk, that is it stores only the string used on a given row, plus a byte or two to encode the length of that string. This is true for both data and indexes.

But once the VARCHAR is loaded out of the storage engine and into memory, it is padded out to its full length. This consumes a lot of memory needlessly if you declare VARCHAR(65535). Then that padded-out representation may end up on disk during sorting or temporary table operations.

So use TEXT. This data type doesn't get padded out in memory like VARCHAR does, and it also supports strings up to 64KB.

If you need longer strings, use MEDIUMTEXT which supports up to 16MB.

answered Feb 13 '13 at 19:12

Bill Karwin

538,548
86
673
828

Does the `VARCHAR` memory consumption apply to **all** queries? Your earlier answer http://stackoverflow.com/a/1962329/1611055 lets me think that it applies only when using `MEMORY` or `temporary` tables (which might certainly be important enough, of course) – Andreas Fester Feb 14 '13 at 08:59
@Andreas, I have since learned (from the Director of Server Development at Percona) that it applies to *all* rows, as they all need to be represented in a storage-independent manner in the SQL layer. – Bill Karwin Feb 14 '13 at 18:22
Thanks Bill - will keep that in mind for future schema designs :) – Andreas Fester Feb 15 '13 at 08:29

score 0 · Answer 2 · answered Feb 13 '13 at 12:23

0

Use VARCHAR.

Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions.

answered Feb 13 '13 at 12:23

Andreas Fester

36,091
7
95
123

score 0 · Answer 3 · edited May 23 '17 at 11:49

one of the fields could be as long as 60kb or short as 100 bytes

Sounds like a BLOB/TEXT. As Andreas has pointed out, you could even use VARCHAR if you know for sure you won't need more than 64K.

I've heard if you define the maximum to be like 60k then every unused space left in the row till 60k will be filled with space

If you use CHAR, the yes: it stores padding.

only few rows would make use of this length while most of the rest rows won't

That's incorrect. Modern DBMSes store variable-length rows: only the space actually needed will be actually used. Just use BLOB/TEXT or VARCHAR and you'll be fine.

Need help in MySQL DB Design (minimizing the overhead)

3 Answers3