Mysql and use of nothing, null, 0

R

Robert9

Well-known member
I have a new addon with four integers for xf_user; no I need a fifth one.
The programer has setup default value = 0; this means that all my 1.020.347.433 users have now four or five new fields with a 0 inside.
And this means to have 1.020.347.433 trillions more bytes to save and load all the time?
Could it be better to save just nothing (and care to make to 0, when needed)?
Or save "Null" instead?
 
I try to know how mysql works with "", 0 and NULL.
Maybe one of this three is just not saved as anything and needs no space?

Else I would think about putting all my five fields in one integer with five digits, saving 0-9 per digit.
 
That's just it, they all require some space if they're adding a new column to the users table. Doesn't matter if they're null or not, an int column takes 1-8 bytes per row depending on its type (tiny = 1, small = 2, medium = 3, int = 4, bigint = 8), null adds a bit on top. That's literally how MySQL works.

If you're adding a string, that's even more complicated, because a varchar string takes a flat minimum of however long the string is plus 1 or more bytes to indicate the length of the string. (Fixed width strings just take the entire defined room and be done with it)

Without you explaining what you're trying to do, it's really not clear what the best approach would be but based on your last line, I'd almost guess that 5x tinyint columns defined as NOT NULL would be the most efficient in terms of space, if you're storing 5x digits. But they're ALWAYS going to consume some space if they're directly in the xf_users table. There's literally no way not to if that's where they are.
 
You must log in or register to reply here.
Top Bottom