[DEV] Tables data types

[BarbzYHOOL]

Why is the column realmlist.population's datatype float unsigned [0] ?

It should be a smallint.

Same for the gamebuild columb int(10) unsigned [12340]

In fact, tons of columns are badly defined and it probably makes mysql slower and takes more disk space for no reason

[NixAJ]

The client expect a float for population. You could make it a u16 for sure, and then cast it when you load it into the database, but you can only have 256 realms either way and at some point you have to weigh in the realistic benefits versus best case scenario. I don't think optimizing away 512 bytes is going to make any difference in speed, especially because the realmlist table is loaded at startup in cache... Its a super cold path, and is not and will never be a bottleneck.

Disk space is cheap, its 2019. It would be a waste of everyone's time, if the focus was on micro optimizations :P

[BarbzYHOOL]

A float for population? like 1.2 player?

The DB is riddled with stuff like that though and if it were done properly from the get go, it would never need to be fixed (means people who did it had no idea what they were doing with datatypes)

[NixAJ]

The client does expect a float so it would make sense to store a float.

It sends a wrong signal when you store 1 datatype but without clarification send another.
I also think that population should be a calculated value based on amount of online players
and an estimated max player count.

5GB is standard free storage on most upload sites, these kind of micro optimizations will spare you less than 30MB in total if you changed all types to the bare minimum, really not worth it.

[BarbzYHOOL]

But any idea why the client expects a float?? as in what would be the goal?

And about the total size, I don't see how you can say 30MB since it totally depends on the size of the DB (I'm more speaking about auth DB and characters DB that continually grow with new players of course, but world is quite big too). I once changed one column for tests and saw tha total size of the table completely differ after that and I was mindblown xD

[NixAJ]

I feel confident in saying 30 MB, because I've personally converted all general types for NovusCore up to their outputted data types, and I've only seen about a 5MB increase for the item_template which went from 75MB to 80MB. Going by this, I would assume that considering creature, creature_template and whatever other small table that may also have a different type sent to the client, the actual saved space would most likely be in the range of 30-100MB, which is barely anything.

The most likely reason as to why the client expects a float is because the guy who implemented the population functionality serverside wasn't the same guy that wrote the handler on the client. It is most likely a silly mistake. (Its not the first time I've seen a questionable DataType differing when it comes to internal use versus actual DataType sent.

Lets assume worst case scenario, every data type was changed to their ACTUAL sent data type, and that for some crazy reason it took up 1GB of storage (I will never get that close), that is nothing compared to what you're actually gettting, and the database won't be noticeably slower at all.

[FrancescoBorzi]

If the client expects certain values in a certain format, that doesn't mean that we necessarily have to store those values in the same format. As a general note: if needed, we can just store any values in a different format and then convert them when sending to the client.

[BarbzYHOOL]

all the comments about "storage is cheap" are worthless to me so no need to insist on this but thank you for your opinion + info

I'm still gonna change them when I work on the db again