[MySQL] Lengte van auto_increment veld van belang?

[Saeverix]

Een leraar vertelde mij iets waar ik aan zat te twijfelen.
Een mede student gaf het ID veld van een tabel een lengte van 100. De leraar vroeg vervolgens waarom hij dat deed. Waarop ik aan de leraar vroeg: Waarom zou je het niet doen? Waarop hij antwoorde:

Als je bij een auto_increment veld een lengte opgeeft dan reserveert hij dit in het geheugen.

Toen vertelde ik hem dat ik wist dat het bij een VARCHAR niet het geval is en dat ik twijfelde aan zijn antwoord. Ik ben gelijk gaan zoeken op internet, maar kon geen direct antwoord vinden.

Daarom vraag ik nu aan jullie: Is het waar dat de MySQL server geheugen reserveert voor de lengte van een auto_increment veld? En waarom zou dit gebeuren?

[flashin]

Niet in het geheugen, maar in je database.

Elke rij wordt groter want hij kan mogelijk meer data bevatten dan nodig is.

[Nielsvr]

Het wordt wel gereserveerd in de database en daarom zonde omdat ik mij niet in kan denken dat jij 10^100 aan rijen gaat krijgen. Zelf doe ik altijd een integer met 11 cijfers, dit geeft mij de mogelijkheid 100 miljard rijden op te slaan, iets wat eigenlijk nog veel te veel is voor het meeste (al heb ik de miljard in verschillende projecten wel al gehaald).

[Snake]

Waarom een ID als varchar? Gewoon een int. Dan kan je 2^32 aantal record bijhouden?

[Peter]

Hah nee. De lengte van een INTEGER veld in MySQL heeft geenszins invloed op de hoeveelheid informatie die je kan opslaan, dit heeft te maken met het type integer wat je gebruikt. Ook signed/unsigned heeft invloed, bij signed integers moet je deze getallen nog delen door twee (en dan nog min één). Onderstaande maximale waardes gaan uit van unsigned integers.

TINYINT - 1 byte, 8 bits, max. 256
SMALLINT - 2 bytes, 16 bits, max. 65.536
INT(EGER) - 4 bytes, 32 bits, max. 4.294.967.296
BIGINT - 8 bytes, 64 bits, max. 18.446.744.073.709.551.616

[JefSnare]

Peter schreef op woensdag 14 oktober 2009 @ 10:31:
Hah nee. De lengte van een INTEGER veld in MySQL heeft geenszins invloed op de hoeveelheid informatie die je kan opslaan, dit heeft te maken met het type integer wat je gebruikt. Ook signed/unsigned heeft invloed, bij signed integers moet je deze getallen nog delen door twee (en dan nog min één). Onderstaande maximale waardes gaan uit van unsigned integers.

TINYINT - 1 byte, 8 bits, max. 256
SMALLINT - 2 bytes, 16 bits, max. 65.536
INT(EGER) - 4 bytes, 32 bits, max. 4.294.967.296
BIGINT - 8 bytes, 64 bits, max. 18.446.744.073.709.551.616

Altijd BIGINT gebruiken dus, zo heb je genoeg ID's voor je rijen in de tabellen.

Ik gebruik nooit dezelfde, bijvoorbeeld voor een blogsite gebruik in INT en voor een nieuwssite BIGINT

[Peter]

Ik zie eigenlijk geen reden om die vier bytes aan extra informatie "op te offeren" voor de mogelijkheid om meer dan 4.3 miljard entries in de database te kunnen zetten. Als je 4.3 miljard entries hebt betekend het dat je al 16 gigabyte aan BIGINT-overhead kwijt bent. En laten we wel wezen, voor wat voor database heb jij zoveel entries nodig?

[Niemand_Anders]

Saeverix schreef op woensdag 14 oktober 2009 @ 10:06:
Een leraar vertelde mij iets waar ik aan zat te twijfelen.
Een mede student gaf het ID veld van een tabel een lengte van 100. De leraar vroeg vervolgens waarom hij dat deed. Waarop ik aan de leraar vroeg: Waarom zou je het niet doen? Waarop hij antwoorde:

[...]

Toen vertelde ik hem dat ik wist dat het bij een VARCHAR niet het geval is en dat ik twijfelde aan zijn antwoord. Ik ben gelijk gaan zoeken op internet, maar kon geen direct antwoord vinden.

Daarom vraag ik nu aan jullie: Is het waar dat de MySQL server geheugen reserveert voor de lengte van een auto_increment veld? En waarom zou dit gebeuren?

Als het goed is zal een primary key niet veranderen. Maar je database zal de lengte van je string + 4 bytes (lengte specificatie) opslaan. Hoe MySQL met veranderende keys omgaat weet ik niet precies, maar ik weet wel hoe MSSQL ermee omgaat en andere databases zullen op een vergelijkbare manier werken.

Als de nieuwe key kleiner is, zal dit geen probleem zijn. Wel heb je dan enkele unused bytes in je entry. Als de nieuwe key groter is, dan maakt de database een entry aan en vervolgens worden de indexen geupdate en je oude entry verwijderd. Daarom hebben databases ook defragmentatie mogelijkheden (pack, dbreindex, vacuumdb, etc) welke alle entries weer netjes achter elkaar zetten en daarmee 'verloren' ruimte weer kan claimen.

Het verhaal wordt wel anders bij een int(10) en een int(100). Vergeet even MySQL en pak een database waarbij de grote van het integer veld via het aantal bytes opgeeft. Een (u)int32 zal elk waarde als 4 bytes opslaan waarbij het niet uitmaakt of de waarde nu 0 is of 4294967295. Een dergelijke primary key zal dus altijd 4 bytes gebruiken ook al past de waarde in slechts 1 byte. Een unsigned 64 bit integer kan een maximale lengte hebben van 20 tekens (18446744073709551615).

Dus als als jij aangeeft dat een integer 100 cijfers moet kunnen bevatten, zal de database deze inderdaad reserveren. Nu ben ik niet precies bekend hoe MySQL omgaat met een int(100), maar ik vermoed dat deze wordt opgeslagen als een (100 / 8 = 12.5 --> 13) 13 byte waarde. In de whitepapers van MySQL is vast wel terug te vinden hoe met dergelijke types intern (mogelijk per storage engine) wordt omgegaan.

[_js_]

Een tabel met een int(100) heeft in MySQL gewoon 4 bytes nodig. Die 100 is het maximaal aantal cijfers dat weergegeven zou moeten worden, maar dat heeft geen invloed op het datatype. Er wordt dus niet meer of minder geheugen gebruikt. In de praktijk wordt het volgens mij niet eens gebruikt (als ik 100 in een tinyint(2) veld doe en ik select dat dan krijg ik gewoon 100 terug en niet 00 ofzo)

[ Voor 27% gewijzigd door _js_ op 14-10-2009 12:00 ]

[MueR]

JefSnare schreef op woensdag 14 oktober 2009 @ 11:04:
Altijd BIGINT gebruiken dus, zo heb je genoeg ID's voor je rijen in de tabellen.

Hardly. Zware overkill, int is meer dan voldoende. Noem mij eens drie situaties waar een bigint nuttig zou zijn?

Ik gebruik nooit dezelfde, bijvoorbeeld voor een blogsite gebruik in INT en voor een nieuwssite BIGINT

Je post een paar duizend nieuwsitems per dag?

[RobertMe]

Zoals hierboven aangegeven, INT(X) in MySQL is onzin, die X geeft aan hoe lang jij wilt dat de string is die eruit komt (ja, het gaat hier nog steeds over een in...). Een applicatie kan zien dat jij een INT(3) hebt, en je waarde maar "2" is, vervolgens kan de app. hier "002" van maken, MySQL kan dit ook automagisch doen met de ZEROFILL optie, maar de vraag blijft, waarom zou je dit willen? En waarom vang je het toevoegen van die 0'en niet in je applicatie af in plaats van in de database.

Staat ook in de MySQL handleiding:

Another extension is supported by MySQL for optionally specifying the display width of integer data types in parentheses following the base keyword for the type (for example, INT(4)). This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)

The display width does not constrain the range of values that can be stored in the column, nor the number of digits that are displayed for values having a width exceeding that specified for the column.

When used in conjunction with the optional extension attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(5) ZEROFILL, a value of 4 is retrieved as 00004.

[Saeverix]

Er kwam dus uiteindelijk uit de mond van de leraar: Als iedereen bij ID een INT(100) opgeeft dan gaat de MySQL server op zijn plaat doordat het geheugen vol loopt.

Die uitspraak vond ik niet geloofwaardig, vandaar dit topic. En volgens mij na het lezen van al deze reacties is het ook onzin. Het heeft geen zin om INT(100) te gebruiken, maar de server zal er geen last van hebben.

Ik zou zelf ook geen INT(100) hebben gekozen, maar het ging mij dus om het feit dat de leraar iets verteld wat onzin is. Die ging uitleggen dat het fout was en dat je er een server mee op zijn plaat kon laten gaan.

Snake schreef op woensdag 14 oktober 2009 @ 10:31:
Waarom een ID als varchar? Gewoon een int. Dan kan je 2^32 aantal record bijhouden?

Dat is ook niet wat ik probeerde te vertellen... Ik vergeleek het met varchar omdat die naar mijn idee geen ruimte reserveert. Maar de leraar verklaarde dat alleen auto_increment velden geheugen reserveerden...

[Creepy]

Ik weet nu niet of jij de info van je leraar verkeerd brengt of dat je leraar het toch niet helemaal bij het rechte eind heeft. Een char(100) bijv zal altijd die 100 tekens bevatten, onafhankelijk van de inhoud. Dus dat kost geheugen en opslagruimte als je er steeds maar een paar tekens in plaatst. Een varchar heeft daar inderdaad geen last van. EN bij een INT is het afhankelijk van het gebruikte type (SMALL, BIG etc).
Dat heeft helemaal niks met auto_increment te maken.

[Cartman!]

Een varchar houdt wel met 1 byte (bij 255 of minder tekens) bij wat de lengte is van de string. Boven de 255 karakters kost je dit 2 byte per veld als ik me niet vergis. 256 ipv. 255 gebruiken bij varchar werkt dus erg nadelig (mits je dat ene karakter net nodig hebt natuurlijk ).

De server zal inderdaad geen last hebben van de INT(100) omdat het puur met de representatie te maken heeft. Als je dat wil zou ik dat zelf ook pas in de presentatielaag toepassen.

[cariolive23]

INT(100) is geen datatype, de waarde 100 slaat nergens op, het geeft alleen iets aan over de weergave. En weergave regel je niet in je database maar in de presentatielaag van de applicatie. En vele applicaties kunnen met dezelfde database werken, het is dus volslagen onzin om in jouw database te gaan bepalen hoe een applicatie e.e.a. moet gaan weergeven. Daar is een database nu net niet voor bedoeld.

Wanneer je bv. VARCHAR(100) gebruikt, geeft de waarde 100 de constraint aan, er mogen niet meer dan 100 karakters in de string staan. Alleen jammer dat MySQL hier standaard weer fout mee omgaat, die maakt er geen constraint van maar een botte bijl: Probeer je meer karakters op te slaan, flikkert MySQL gewoon een deel van je data weg en zit jij met de gebakken peren. Ga de boel dus fatsoenlijk configureren, anders kan het zomaar zijn dat je met een corrupte database zit te werken, het kan zomaar zijn dat je reeds een deel van jouw data kwijt bent. En die kun je niet meer herstellen, ook de backups zijn onbruikbaar.

[Saeverix]

Creepy schreef op woensdag 14 oktober 2009 @ 17:19:
Ik weet nu niet of jij de info van je leraar verkeerd brengt of dat je leraar het toch niet helemaal bij het rechte eind heeft. Een char(100) bijv zal altijd die 100 tekens bevatten, onafhankelijk van de inhoud. Dus dat kost geheugen en opslagruimte als je er steeds maar een paar tekens in plaatst. Een varchar heeft daar inderdaad geen last van. EN bij een INT is het afhankelijk van het gebruikte type (SMALL, BIG etc).
Dat heeft helemaal niks met auto_increment te maken.

Het ging om een ID in een table die een leerling op INT(100) had gezet.
De leraar gaf toen aan dat je daarmee het geheugen van de database server overbelast waardoor de server kan crashen.

En hij hield zijn poot stijf toen ik er wat vragen over stelde. Maar van die 13 bytes die het dan zou reserveren gaat een database server niet moeilijk van kijken lijkt mij...

Ik ben het er natuurlijk wel mee eens dat INT(100) totaal geen zin heeft, maar om dan uit te gaan leggen dat het FOUT is en dat je de server er mee down haalt vond ik onzin.

[MueR]

Het is onzinnig, verder niks. Geen negatieve performance bij mijn beste weten.

[sky-]

Wat heeft auto_increment met PHP te maken? Dat is MySQL.

[Gomez12]

Saeverix schreef op woensdag 14 oktober 2009 @ 22:55:
[...]

Het ging om een ID in een table die een leerling op INT(100) had gezet.
De leraar gaf toen aan dat je daarmee het geheugen van de database server overbelast waardoor de server kan crashen.

En hij hield zijn poot stijf toen ik er wat vragen over stelde. Maar van die 13 bytes die het dan zou reserveren gaat een database server niet moeilijk van kijken lijkt mij...

Ik ben het er natuurlijk wel mee eens dat INT(100) totaal geen zin heeft, maar om dan uit te gaan leggen dat het FOUT is en dat je de server er mee down haalt vond ik onzin.

Tja, toch vermoed ik dat hij puur theoretisch wel een punt heeft. Die representatie gegevens moeten toch ergens bijgehouden worden en dat kost extra geheugen ruimte.
In de praktijk geld dit enkel maar als je op een vic-20 of iets dergelijks zit te programmeren ( wie kent hem nog ) op hedendaagse geheugen groottes is het verwaarloosbaar ( maar toch aanwezig en dus niet 100% onzin, slechts 99,999999999999999999% )

Maar bovenal als het een student is dan is het imho ook gewoon fout te keuren door een docent, het is overbodig / vervuilende info die de leesbaarheid verstoort.
Imho is het net zoiets als random spaties voor een regel zetten in een programmeertaal, het kan an sich geen kwaad ( je compiler negeert het toch ) maar het is gewoon verkeerd handelen.

Een docent hoort mensen het goede bij te brengen en daarvoor hoeft iets wat afgekeurd wordt niet perse fout te zijn,niet goed genoeg / onduidelijk is wat mij betreft ook genoeg om iets af te keuren...

[.oisyn]

Gomez12 schreef op donderdag 15 oktober 2009 @ 00:32:
Die representatie gegevens moeten toch ergens bijgehouden worden en dat kost extra geheugen ruimte.

Ja, in de metadata van de tabel, waarbij het dus niet uitmaakt of het datatype van je kolom een INT(1) of een INT(100) is, want in beide gevallen moet hij opslaan dat het een INT is en wat de lengte is. Ergo, het verschil is 0. Niet heel klein, maar letterlijk 0.

[ Voor 14% gewijzigd door .oisyn op 15-10-2009 00:35 ]

[keesdewit]

JefSnare schreef op woensdag 14 oktober 2009 @ 11:04:
[...]


Altijd BIGINT gebruiken dus, zo heb je genoeg ID's voor je rijen in de tabellen.

Ik gebruik nooit dezelfde, bijvoorbeeld voor een blogsite gebruik in INT en voor een nieuwssite BIGINT

Nee dat is lekker efficient!

Je kan het beste de data types zo laag mogelijk inzetten (wel schaalbaar genoeg) omdat dit meer ruimte in neemt op bestands niveau. Het heeft alles te maken met het reserveren van ruimte.