Re: Does a blank field take up disk space?

From: Axel Schwenke <axel.schwenke_at_gmx.de>
Date: Mon, 1 Aug 2016 12:18:49 +0200
Message-ID: <nnn7m9$fa1$1_at_dont-email.me>


On 29.07.2016 16:23, bit-naughty_at_hotmail.com wrote:
> If I have a field, say "Price", and there has been nothing put into that field
> in a particular line (but there may be something in that field on *other* lines),
> I assume it doesn't take up space on the disk?

A lot has been said in this thread, but none of the answers is really great. It starts with the fact that there is no such thing as an "empty" or "blank" field in a database. A field always contains something and in the case of SQL databases this something might be NULL.

If you don't know the concept of NULL - stop reading here and grab a text book first. Then come back and continue reading.

Now many databases have optimizations to store NULL fields efficiently. Specifically MySQL /can/ collapse a NULL field to a single bit. If it will use that optimization, depends on details. I.e. MyISAM will not collapse NULL fields if the table layout uses constant length rows. InnoDB will not collapse NULL fields if the REDUNDANT row format is configured. etc. pp. So much for the case of fields containing NULL.

Now if there is a non-NULL value in a field, the storage requirements of that field can still vary depending on the value and the data type of the field. The obvious example is a VARCHAR field containing the empty string. Even if the field is declared as VARCHAR(100), it will not eat 100 bytes in that case. Instead VARCHAR stores a length plus a buffer of that length. And for the empty string it would store the number 0 for the length and no buffer at all.

Now the example was for a field called `Price` and one would assume that such a field uses a numeric data type. The numeric types in MySQL however have a constant storage size, independent of the actual content of the field. I.e. the value 0 would take the same space as the value 47.11.

On top of all that comes another topic: compression. InnoDB nowadays can compress data on the fly. Then the footprint on disk can again depend on the actual content of data fields.

[Quoted] Finally: it has been asked why this concerns you. Your answer was not good. It doesn't matter if you have hundreds of millions of rows. At least it doesn't matter alone. What also matters, is what else is in your rows. If every row contains something like 1000 bytes of real data then you wouldn't much care for 4 extra bytes being eaten by your `Price` field. But if a row is only 10 bytes, then it's a completely different story. Received on Mon Aug 01 2016 - 12:18:49 CEST

Original text of this message