Data type MySQL database

[ericplan]

Mijn vraag
Een huidige database voor registratie van PV-opbrengsten moet geherstructureerd en slimmer.
Twee velden in deze database zijn nu van het type float.
Veld 1 bevat getallen van 0-2000
Veld 2 bevat getallen van 0,000-0,500

Relevante software en hardware die ik gebruik
MySQL op een externe server bij een webhoster.

Wat ik al gevonden of geprobeerd heb
SmalINT lijkt een betere keuze voor veld 1
Decimal of float voor veld 2, maar welke?

De huidige database bevat inmiddels ruim 1.000.000 regels (in 8 jaar) en moet nog even mee. De vraag is dus ook bedoeld om de databasegrootte beperkt te houden.

[HollowGamer]

http://stackoverflow.com/...oat-and-decimal-data-type
https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

[Woy]

ericplan schreef op donderdag 20 april 2017 @ 15:38:
Veld 2 bevat getallen van 0,000-0,500
..
Decimal of float voor veld 2, maar welke?

Het is een beetje afhankelijk van wat de brondata is. Decimal is ( zoals de naam al aangeeft ) vooral geschikt voor getallen die decimaal te representeren zijn. Floating point getallen (IEEE 754) worden op een andere manier opgeslagen, en hebben dus andere karakteristieken waardoor je niet alle decimale getallen kunt representeren.

De huidige database bevat inmiddels ruim 1.000.000 regels (in 8 jaar) en moet nog even mee. De vraag is dus ook bedoeld om de databasegrootte beperkt te houden.

Voorzie je dan al problemen? Op zich zijn 1.000.000 regels niet zo veel.

[Hydra]

ericplan schreef op donderdag 20 april 2017 @ 15:38:

De huidige database bevat inmiddels ruim 1.000.000 regels (in 8 jaar) en moet nog even mee. De vraag is dus ook bedoeld om de databasegrootte beperkt te houden.

Je hebt het hier bij een 32-bit integer over 4 megabyte aan data he. Dat is echt compleet niks. Je bent behoorlijk tijd aan 't verspillen.

Sterker nog; veel databases slaan een dergelijke tinyint gewoon op als 32 of 64 bits integer omdat 't voor de CPU sneller is met 'native' integers te werken.

[ericplan]

De huidige database is 104 MB groot en omdat er niet zo efficient wordt geschreven klaagt mijn webhoster over CPU-load. Er staat veel informatie dubbel of driedubbel in.
Als ik dan toch moet herstructureren, dan direct de beste keus. Bedoeling is om dubbele informatie te vermijden en om efficienter weg te schrijven.

[Hydra]

ericplan schreef op donderdag 20 april 2017 @ 16:37:
De huidige database is 104 MB groot en omdat er niet zo efficient wordt geschreven klaagt mijn webhoster over CPU-load. Er staat veel informatie dubbel of driedubbel in.

Lijkt me dan eerder een kwestie van verkeerd gebruik van indices.

[ACM]

Hydra schreef op donderdag 20 april 2017 @ 15:56:
Je hebt het hier bij een 32-bit integer over 4 megabyte aan data he. Dat is echt compleet niks. Je bent behoorlijk tijd aan 't verspillen.

Hoewel je hier op zich gelijk mee hebt, geldt natuurlijk wel: hoe kleiner de records, hoe meer er met 1 disk-read (of ram-access) kunnen worden ingelezen.

Sterker nog; veel databases slaan een dergelijke tinyint gewoon op als 32 of 64 bits integer omdat 't voor de CPU sneller is met 'native' integers te werken.

Aangezien de specifieke database al genoemd is - MySQL - heeft deze opmerking weinig nut

Maar eerlijk gezegd geloof ik je statement ook niet. Van zowel MySQL als PostgreSQL weet ik in ieder geval zeker dat ze gewoon zoveel bits gebruiken als nodig is voor het datatype en niet onnodig bytes verspillen.
Of ze diezelfde representatie ook in RAM (altijd) zo klein houden weet ik niet, het zou me inderdaad niet verbazen als e.e.a. daar wel naar native ints wordt geconverteerd.

In dit verhaal is met name MySQL's 3-byte grote mediumint veel interessanter. Daar is vziw in programmeertalen en cpu's doorgaans geen ondersteuning voor, dus dat moet dan sowieso geconverteerd worden naar 4-byte integers. Voor smallint is normaliter in ieder geval nog een 'short' beschikbaar (en voor tinyint de 'byte').

Overigens is PostgreSQL (en ik) het verder wel met je eens:

quote: https://www.postgresql.or...tic/datatype-numeric.html

The type integer is the common choice, as it offers the best balance between range, storage size, and performance. The smallint type is generally only used if disk space is at a premium. The bigint type is designed to be used when the range of the integer type is insufficient.

Hoedanook, terugkerend naar de originele vraag:
Veld1 kan met zowel integer als smallint.
De integer zal qua rekenwerk wat vlotter zijn, de smallint levert kleinere on-disk data op (zowel voor dat als voor indexen).

Veld2 kan met de standaardopties float en decimal, maar eventueel ook met 1000 vermenigvuldigd worden end an ook als integer.
Float is waarschijnlijk het eenvoudigst en efficienter met rekenen (er is native cpu-ondersteuning voor) dan de decimal. Het verliest wel enige preciesie, omdat de float-representatie domweg niet alle mogelijke cijfers kan opslaan. Echter door dat weer af te ronden, zal je daar in de praktijk wellicht weinig van merken. Qua opslag lijkt het erop dat ze beide in 4 bytes passen.
Decimal heeft als voordeel dat het gegarandeerd precies is. Maar er is geen native cpu-ondersteuning voor, dus de kans is groot dat het tijdens je programmeerwerk alsnog in float (of double) wordt omgezet. Qua opslag is waarschijnlijk ook 4 bytes. Wel heeft dat wat complexere conversie dan domweg de vier bytes van een float opslaan, dus het is ook hier trager.
Smallint en gewone integer kunnen ook. Door het met 1000 te vermenigvuldigen eindig je dan met waardes 0-500. Verder gelden dan dezelfde argumenten als bij Veld1.

Kortom;
Als opslagruimte je drijfveer is: gebruik voor beide smallint.
Als performance belangrijk is: gebruik voor veld1 integer en voor veld2 float.
Als preciesie echt belangrijk is: gebruik voor veld1 een integer-smaak en voor veld2 decimal (let wel op hoe je er dan in je code mee omgaat).

[ericplan]

Dank, het worden smallints. Het kommagetal wordt van kWh geconverteerd naar Wh en raakt daarmee z'n komma kwijt. Dat is een éénmalige actie op de bestaande data en bijna geen werk voor de nieuwe database. Ik zal jullie nog laten weten wat de actie aan MB heeft opgeleverd. Het aantal regels/records is met deze actie al tot 1/3 teruggebracht.

[gekkie]

ACM schreef op donderdag 20 april 2017 @ 18:05:
[...]
Overigens is PostgreSQL (en ik) het verder wel met je eens:

Let wel, dat het in iedergeval bij postgres nog een tikkie complexer kan liggen soms.
Je hebt namelijk ook nog te maken met padding en afhankelijk van de gekozen columntypes EN volgorde van de columns zal er wel of geen padding plaats vinden en zul je dus ook wel of geen reductie van je rowsize krijgen (yup it bit me once ).

typalign is the alignment required when storing a value of this type. It applies to storage on disk as well as most representations of the value inside PostgreSQL. When multiple values are stored consecutively, such as in the representation of a complete row on disk, padding is inserted before a datum of this type so that it begins on the specified boundary. The alignment reference is the beginning of the first datum in the sequence.

Possible values are:

c = char alignment, i.e., no alignment needed.

s = short alignment (2 bytes on most machines).

i = int alignment (4 bytes on most machines).

d = double alignment (8 bytes on many machines, but by no means all).

Wat voor een bepaald columntype geldt is te vinden in de system table "pg_type"