[MySQL] Relational vs. Enum

[Firesphere]

Op welk punt is een relational database handiger dan een Enum?

Ik heb geen goed antwoord kunnen vinden helaas. Waar het, wat deze casus betreft, om een database, die een tabel heeft, laten we het "tabel1" noemen. Het betreft een table met afbeeldingen.

Deze tabel heeft een enum met "Poster,Screenshot" als eigenschap.

Nu vraag ik mij af, zo langzamerhand zitten er een paar duizend records in die database. Is het effectiever om alles te refactoren naar een "ImageType" database, en Tabel1 een reference te laten hebben, of maakt het qua overhead/snelheid niets uit.

[HMS]

Waarom zou je het willen refactoren? Is er een reden voor deze change? Ik zou refactoren overwegen zodra een afbeelding meerdere waardes van de enum kan hebben. KISS

[Firesphere]

HMS schreef op maandag 23 december 2013 @ 21:23:
Waarom zou je het willen refactoren? Is er een reden voor deze change? Ik zou refactoren overwegen zodra een afbeelding meerdere waardes van de enum kan hebben. KISS

Nou, behalve "omdat het kan", vooral om overhead te voorkomen, ik heb nu een paar duizend records met "type" van Poster.

Als een relational DB met indexes sneller is en minder ruimte in beslag neemt, is het weer een kleine optimalisatie
Wat ik alleen vrees, is 80 extra queries per view. En dan is de overhead van een enum misschien acceptabel.

[ Voor 9% gewijzigd door Firesphere op 23-12-2013 21:29 ]

[HMS]

*insert standaard rant over premature optimization hier*

Zo, nu we dat gehad hebben ...

De vraag is wat precies de overhead is, en waar dat zit. Ik weet niet welk datatype je enum nu heeft (string?) en wat je als FK wilde gaan gebruiken. Gaat het hier om overhead in indexes?

Onthoud wel dat als je er een aparte tabel van maakt, dat je weer een JOIN nodig hebt (of een extra query , of je moet in het geheugen houden welke waarde bij welke enum waarde hoort aan de client kant).

Daarnaast is een paar duizend records natuurlijk niet erg veel voor een modern RDBMS op moderne hardware.

[djluc]

Een enum is goedkoper in zoeken als een join dus je hebt de juiste oplossing tenzij je veel opties bij wilt gaan maken. Bijmaken is heel duur (omdat je dan een alter table hebt).

[Verwijderd]

Een enum in MySQL is gewoon een INT, veel simpeler gaat het niet worden. Daar zit echt geen overhead in, zeker minder dan een foreign key (voor zo ver daar ondersteuning voor is, afhankelijk van de gebruikte engine) met bijbehorende andere entiteit.

[orf]

Mysql geeft als mogelijke optimaliseermethode juist altijd het aanpassen van een kolom naar een enum als er niet veel verschillende values zijn. Intern is een enum een INT als achterliggende waarde. Bij het inserten zet mysql je waarde om naar de int en bij een select pas weer terug.

Een enum aanpassen is wel wat vervelend.

edit: Spuit 11

[ Voor 5% gewijzigd door orf op 23-12-2013 21:34 ]

[HMS]

Ah, een enum is een int. Met een beetje creativiteit kun je dus ook gewoon met bitjes gaan werken zodra een enum meerdere waardes kan hebben, niet dat dat duidelijker is mocht je ooit iets uit de database willen redden zonder de bijbehorende code .

[Verwijderd]

HMS schreef op maandag 23 december 2013 @ 21:36:
Ah, een enum is een int. Met een beetje creativiteit kun je dus ook gewoon met bitjes gaan werken zodra een enum meerdere waardes kan hebben, niet dat dat duidelijker is mocht je ooit iets uit de database willen redden zonder de bijbehorende code .

Een enum is een INT voor de engine, voor de buitenwereld is het gewoon een enum. Maar de kosten van gebruik zijn dus vergelijkbaar met een INT.

Wijzigingen zijn kostbaar, de vraag is dus hoevaak je die hebt.

@TS:
Waarom denk je eigenlijk dat een enum overhead heeft? Waar haal je dat vandaan?

[Firesphere]

Verwijderd schreef op maandag 23 december 2013 @ 21:37:
[...]

Een enum is een INT voor de engine, voor de buitenwereld is het gewoon een enum. Maar de kosten van gebruik zijn dus vergelijkbaar met een INT.

Wijzigingen zijn kostbaar, de vraag is dus hoevaak je die hebt.

@TS:
Waarom denk je eigenlijk dat een enum overhead heeft? Waar haal je dat vandaan?

Ik zeg niet dat het zo is, ik vraag me alleen af welke beter is, enum("poster,screenshot") vs. typeID=1 & typeID=2

[orf]

De ene (enum) is sneller en lichter (slecht 1 byte per row in jouw geval). De andere is wat makkelijker te onderhouden als er mogelijke waardes bij komen.

Ik snap niet helemaal waarom je ook 80 extra queries per view hebt als je het ombouwt naar een relationele tabel.

[Firesphere]

orf schreef op maandag 23 december 2013 @ 21:48:
Ik snap niet helemaal waarom je ook 80 extra queries per view hebt als je het ombouwt naar een relationele tabel.

Omdat voor elk item een nieuwe "check if relation X or relation Y" nodig is. En er ongeveer 80 items op de page staan

Wat ik mis zie gaan, om even duidelijk te zijn, de huidige situatie is niet een enum("0,1") maar een enum("poster,screenshot")

[orf]

Firesphere schreef op maandag 23 december 2013 @ 21:54:
Wat ik mis zie gaan, om even duidelijk te zijn, de huidige situatie is niet een enum("0,1") maar een enum("poster,screenshot")

Het maakt niet uit welke string je in je enum stopt. Het gaat erom wat mysql intern gebruikt. Dat staat ook gewoon in de documentatie en de FAQ.

[ Voor 12% gewijzigd door orf op 23-12-2013 22:07 ]

[Cartman!]

Dus om te bepalen of iets X of Y is heb je 80 queries nodig? Dat kun je toch 1 keer checken en dan cachen? Of aangezien je nu maar 2 waarden hebt gewoon hardcoden?

[Verwijderd]

Ik denk dat je eens goed moet gaan kijken naar je hele opzet ipv de focus te leggen op het datatype van 1 kolom. Als een screenshot en een poster zichzelf anders gedragen zijn het dus gewoon andere entiteiten, hoe zeer die ook lijken op elkaar qua datamodel. Je zou bijvoorbeeld een entiteit poster en een entiteit screenshot kunnen hebben welke een gezamelijke ouder hebben waarin de overeenkomstige data en eventueel functionaliteit zit, de verschillen zitten dan in de losse entiteiten.

[Gomez12]

Firesphere schreef op maandag 23 december 2013 @ 21:46:
[...]

Ik zeg niet dat het zo is, ik vraag me alleen af welke beter is, enum("poster,screenshot") vs. typeID=1 & typeID=2

Aangezien een enum intern bij mysql simpelweg een int is zit er geen groot verschil in dit voorbeeld (er zit wel ietwat verschil in aangezien een enum eerst inten nog omgezet moet worden naar een int, maar ik vraag me af je dat ooit gaat kunnen meten)

Echter als je naar het vervolg gaat kijken dan kom je uit op :
Enum : 1 goed geoptimaliseerde omzetting van int naar string.
Int : een extra join met de bijkomende kosten

Dus de int gaat sowieso duurder zijn, maar wat ook nog mee zou kunnen tellen (ik weet even de huidige staat van MySQL niet) is dat MySQL vroeger maar 1 index per tabel kon gebruiken en dan gaat een int helemaal extreem duur worden.

Maar dat is de theorie, nu even jouw praktijk : Definieer eens "een paar duizend records"...
Want als het is wat ik denk (in de orde van grootte van <10.000 records) dan is het niet relevant. Ga je naar een orde van grootte van > 100 miljoen records dan zou ik enum aanraden.
Ik vermoed dat dit gewoon een gevalletje gaat worden van premature optimalisation.

Maar om nog even terug te komen op je beginvraag : Op welk punt is een relational database handiger dan een Enum?
Op onderhoudbaarheid, voor de rest is een enum veel handiger / efficienter.
Alleen is die onderhoudbaarheid wel een belangrijk punt (een enum aanpassen is altijd alter table en dat is erg erg kostbaar op een grotere database).
Dus voorzie je dat de waardes de komende 5 jaar verandert moeten gaan worden dan zou ik voor relational gaan, voorzie je dat de waardes pas over 10 jaar verandert moeten worden dan zou ik gaan twijfelen (wellicht komt er nog een refactor ronde en dan kan je nu rustig voor enum gaan en refactor je het later), voorzie je dat de waardes pas over >15 jaar verandert moeten worden : Enum

[Douweegbertje]

Cartman! schreef op maandag 23 december 2013 @ 21:57:
Dus om te bepalen of iets X of Y is heb je 80 queries nodig? Dat kun je toch 1 keer checken en dan cachen? Of aangezien je nu maar 2 waarden hebt gewoon hardcoden?

Nee, omdat je het per item (1 query) moet checken. Als je dan 80 items (80 queries) hebt heeft al nut als je 0.1% optimalisatie hebt.


Maar in elk geval als ik het zo snel even lees, gooi je toch gewoon de items onder de poster? En niet de poster bij het item? Nja het is ook laat, misschien lees ik alles niet goed

[ Voor 17% gewijzigd door Douweegbertje op 23-12-2013 22:04 ]

[Gomez12]

Douweegbertje schreef op maandag 23 december 2013 @ 22:02:
[...]
Nee, omdat je het per item (1 query) moet checken. Als je dan 80 items (80 queries) hebt heeft al nut als je 0.1% optimalisatie hebt.

TS heeft het over "een paar duizend records", de optimalisatie gaat dan ergens in de richting van 0,00000000001% procent oid liggen.

Als je wil gaan optimaliseren kan je beter bedenken hoe je van die 80 query's afkomt en naar 1 (of in ieder geval een hoop minder) query gaat. De latency-winst gaat waarschijnlijk een optimalisatie van >800% opleveren.

80 query's betekent 80 keer iets naar de db sturen, wachten op antwoord en dan pas verder gaan

[Firesphere]

Douweegbertje schreef op maandag 23 december 2013 @ 22:02:
[...]


Nee, omdat je het per item (1 query) moet checken. Als je dan 80 items (80 queries) hebt heeft al nut als je 0.1% optimalisatie hebt.


Maar in elk geval als ik het zo snel even lees, gooi je toch gewoon de items onder de poster? En niet de poster bij het item? Nja het is ook laat, misschien lees ik alles niet goed

Ik heb X items. In dit geval zo'n 8000 items.
Alle items hebben 1 of meer images.
Deze hebben dus allemaal minstens 1, zo niet meer images van het type Poster of Screenshot.
Het totaal aantal afbeeldingen loopt richting de 20k. (valt nog mee)

Wat ik wil weten, is vooral, op welk punt is een extra relatie naar een "imagetype" database relevant. Het boeit me niet of het er toe doet.

En omdat ik beter nu kan refactoren naar een extra DB, dan nu de boel op z'n beloop laten, wil ik graag weten wat er beter is

[Cartman!]

Douweegbertje schreef op maandag 23 december 2013 @ 22:02:
[...]
Nee, omdat je het per item (1 query) moet checken. Als je dan 80 items (80 queries) hebt heeft al nut als je 0.1% optimalisatie hebt.

De TS heeft het over een waarde die X of Y is, dat kun je heel makkelijk hardcoden. Mag toch hopen dat je niet 8000 keer een query gaat doen om te bepalen dat 1 gelijk is aan poster en 2 gelijk aan screenshot.

[Gomez12]

Firesphere schreef op maandag 23 december 2013 @ 22:18:
[...]
Ik heb X items. In dit geval zo'n 8000 items.
Alle items hebben 1 of meer images.
Deze hebben dus allemaal minstens 1, zo niet meer images van het type Poster of Screenshot.
Het totaal aantal afbeeldingen loopt richting de 20k. (valt nog mee)

Wat ik wil weten, is vooral, op welk punt is een extra relatie naar een "imagetype" database relevant. Het boeit me niet of het er toe doet.

Performance technisch boeit het niet, maar onderhoudstechnisch zou ik het gewoon nu al omgooien naar imagetype. Ik ken niet de exacte vulling / doelstelling, maar ik vermoed dat er binnenkort een extra type bij moet "Artwork" en dan over een half jaar weer een extra type "MoviePoster" oid.

Met een imagetype tabel kan je vrij alles invoegen en er ook extra metadata aanhangen (je kan bijv een largescreenshot / smallscreenshot introduceren met maten in je imagetype tabel) terwijl je met een enum bij elke wijziging dure operaties moet uitvoeren.

Persoonlijk gebruik ik enkel enums voor waarden waarvan ik echt 100% zeker weet dat ze de komende 15 jaar niet gaan veranderen voor de rest een imagetype tabel.
Performance gewijs is het verschil niet zo heel erg groot (totdat je echt gaat dataminen / data ware house structuren gaat opzetten maar dan ga je ook over andere dingen praten) maar bij de een kan je iets nieuws introduceren zonder moeite en bij de ander kost elke aanpassing giga-veel tijd / ongemak / downtime.

[Gomez12]

Cartman! schreef op maandag 23 december 2013 @ 22:21:
[...]

De TS heeft het over een waarde die X of Y is, dat kun je heel makkelijk hardcoden. Mag toch hopen dat je niet 8000 keer een query gaat doen om te bepalen dat 1 gelijk is aan poster en 2 gelijk aan screenshot.

Ik mag toch hopen dat je niet serieus zoiets wilt hardcoden, dan ben je wmb helemaal van het pad af.

En het zou geen 8000x een losse query zijn, maar 8000x een extra join in een query (want je wilt toch hetzelfde resultaat teruggeven)

Hardcoden heeft echt zo ongeveer een onderhoudbaarheid van 0,0

[Voutloos]

Douweegbertje schreef op maandag 23 december 2013 @ 22:02:
Nee, omdat je het per item (1 query) moet checken. Als je dan 80 items (80 queries) hebt heeft al nut als je 0.1% optimalisatie hebt.

offtopic:
Nee, nope, echt niet, heel no. Het blijft een micro optimalisatie. Je praat hier over 1/1000e besparing, terwijl een betere aanpak/algoritme misschien 79/80e kan besparen door minder queries te doen. En zelfs voordat je over die grote besparing nadenkt zou je al moeten onderbouwen dat het nodig is en daadwerkelijk beter werkt. Meten = weten.

Mijn gok is dat ts over extra queries begint ivm weinig ervaring met joins, en dat zou dan goed leesvoer zijn trouwens. Wel zo leuk in een relationele database.

Meten = weten is dan wel relevant. Alhoewel het alvast een gegeven is: een ENUM is bedoeld voor een constante set van beperkt aantal mogelijke waarden. Wordt de set groter, of is deze aan verandering onderhevig, of als een 'imageType' meer properties heeft, is een join praktischer of gewoon nodig. En dat is dan eigenlijk niet eens een performance (luxe-) probleem.

[ Voor 7% gewijzigd door Voutloos op 23-12-2013 23:07 ]

[NMe]

Firesphere schreef op maandag 23 december 2013 @ 22:18:
[...]

Wat ik wil weten, is vooral, op welk punt is een extra relatie naar een "imagetype" database relevant. Het boeit me niet of het er toe doet.

Het punt waarop je aan kan zien komen dat je regelmatig vele imagetypes moet gaan toevoegen. Op het moment dat vast staat dat er twee types zijn en er voor 100% zeker is dat dat het komende jaar zo zal blijven en vrij zeker is dat het in de twee jaar daarna ook zo blijft, laat het dan lekker een enum blijven.

[Cartman!]

Gomez12 schreef op maandag 23 december 2013 @ 22:30:
[...]

Ik mag toch hopen dat je niet serieus zoiets wilt hardcoden, dan ben je wmb helemaal van het pad af.

En het zou geen 8000x een losse query zijn, maar 8000x een extra join in een query (want je wilt toch hetzelfde resultaat teruggeven)

Hardcoden heeft echt zo ongeveer een onderhoudbaarheid van 0,0

Oneens. Als het gaat om 2 keuzes zou ik het zeker hardcoden als performance belangrijk is. Als pergormance niks boeit lekker alles met ORM doen.

[Gomez12]

Cartman! schreef op dinsdag 24 december 2013 @ 08:29:
[...]

Oneens. Als het gaat om 2 keuzes zou ik het zeker hardcoden als performance belangrijk is. Als pergormance niks boeit lekker alles met ORM doen.

Waar denk je exact dat die performance inzit? Met een enum doet je dbase server hetzelfde als jij hardcoded wilt doen, alleen zal je dbase server het sneller kunnen doen (want geoptimaliseerder). Dan heb je nog wel het feit dat er hardcoded enkel maar een int over de lijn gaat terwijl je met een enum een string terugkrijgt.

Dit zijn echt theoretisch micro-optimalisaties waarvan ik betwijfel of ze in de praktijk niet gewoon tegen je werken terwijl je qua onderhoudbaarheid opeens 8500 stappen terug hebt gezet.

Een enum is juist gemaakt zodat je het niet hardcoded hoeft te doen, ik verwacht dan ook dat deze geoptimaliseerder is dan welke zelfgebrouwen functie dan ook.

[NMe]

Enums hardcoden is inderdaad zinloos. Dat is niet meer dan een integer met een klein beetje table-level overhead om elke toegestane intwaarde een string-label te geven. Vervolgens wordt je query er een stuk leesbaarder van terwijl je performance er niet onder lijdt. Je moet hoe dan ook die 1 of die 2 opslaan; of je dat als integer of als enum (=integer) doet maakt daarbij geen bal uit.

[Cartman!]

Ik snap de verwarring, volgens mijn zijn dit de opties:

- enum
- foreighn key

In het geval dat je nu 2 types hebt en enums gebruikt: lekker zo laten. Wil je perse een losse tabel hebben dan kun je elke keer n query doen die altijd op 1 van de 2 types uitkomt. In dat geval zou ik gewoon een functie maken op mn entity die adhv het id de juiste soort teruggeeft zodat je niet 8000 dezelfde query's zit te doen. Of als performance niet boeit ORM dat werk laten doen zodat ie maar 2 keer n query hoeft te doen ipv 8000. Hoop dat t zo duidelijker is

[Hydra]

Ik snap niet helemaal waar het idee vandaan komt dat een enum om een of andere reden 'trager' zou zijn dan een aparte tabel. Het is niks meer dan een 'leesbare' integerwaarde. Als je het in een externe tabel gaat doen ga je alleen maar extra indexen raken. Dan ga je (vermoed ik) van:

Tabel item
- id
- type ('poster', 'screenshot')

Naar
Tabel item:
- id (int)
- type (int, fk naar item_types)

Tabel item_types:
- id (int)
- name (varchar)

Dan doe je een join op beide tabellen waarbij je filtert op item_types.naam; dan ga je dus 2 indexen (de FK en de index op 'item_types.naam' raken in plaats van een enkele index op item.type. Ik snap niet hoe je erop komt dat dat efficienter zou zijn.

[NMe]

Het voordeel zit sowieso niet in de efficiëntie nee, wel in de onderhoudbaarheid. Maar alleen als het aannemelijk is dat het aantal types op afzienbare termijn uitgebreid moet worden.

[Firesphere]

NMe schreef op dinsdag 24 december 2013 @ 15:37:
Het voordeel zit sowieso niet in de efficiëntie nee, wel in de onderhoudbaarheid. Maar alleen als het aannemelijk is dat het aantal types op afzienbare termijn uitgebreid moet worden.

De kans dat er andere types o.i.d. bij komen is zeer klein, en als het al gebeurd, zullen het nooit meer dan 3 worden.

Eigenlijk, denk ik dat de enige reden om het van enum naar orm te zetten zin heeft, als ik meer met de enums wil doen dan het onderscheid tussen de afbeeldingen aan te geven.

Hydra:
Ik heb nooit gedacht dat het perse beter was, ik was gewoon nieuwsgierig in hoeverre het uit maakt. Grotendeels vooral omdat de enum een tekstuele representatie is, en dus een tekstuele overhead heeft ergens.

[NMe]

Het maakt sowieso bar weinig uit. Enums zijn marginaal beter maar tenzij je tabellen in de miljoenen records lopen ga je amper verschil merken.

[Hydra]

Firesphere schreef op dinsdag 24 december 2013 @ 15:40:
Hydra:
Ik heb nooit gedacht dat het perse beter was, ik was gewoon nieuwsgierig in hoeverre het uit maakt. Grotendeels vooral omdat de enum een tekstuele representatie is, en dus een tekstuele overhead heeft ergens.

Maar dat heb je ook als je een types tabel hebt met type id en type name en op die name gaat selecteren. Als je op pure IDs gaat selecteren heb je ook die aparte tabel niet nodig.

Een simpele hash-lookup is bovendien volledig verwaarloosbaar.

NMe schreef op dinsdag 24 december 2013 @ 15:42:
Het maakt sowieso bar weinig uit. Enums zijn marginaal beter maar tenzij je tabellen in de miljoenen records lopen ga je amper verschil merken.

Een enum-lookup is O(1), intern werkt MySQL gewoon met integers. Dus bij grote tabellen zal een join waarschijnlijk veel inefficienter zijn. Relatief gezien dan, dit zijn zulke simpele queries dat je er niks van gaat merken.

[ Voor 29% gewijzigd door Hydra op 24-12-2013 15:46 ]