Datamodel voor meerdere gastenboeken*

[Tino1986]

Stel: ik zou een website willen maken die gratis gastenboeken aanbied, zoals er zoveel zijn (gastenboek.be, 123gastenboek.nl, koekjes.net, ...). Ik heb daarvoor dan een databank nodig om de verschillende gastenboeken en bijhorende berichten in op te slaan. Maar ik zit met enkele bedenkingen i.v.m. het onwerp van de databank.

Het eenvoudigste lijkt mij:

Bericht	Gastenboek
Een beetje tekst...	boek1
Nog een beetje tekst...	boek1
Wat meer tekst...	boek2
Bla...	boek2
Hallowkes...	boek2
...	boek3
En nog wat...	boek3

Als ik dan alle berichten van bvb. boek2 zou willen tonen, zou ik volgend statement kunnen gebruiken:

code:

SELECT * FROM gastenboeken WHERE gastenboek = "boek2"

Dat werkt zo zonder probleem. Maar ik maak me (misschien onterecht) zorgen over de performantie van deze manier van aanpak. Wat als ik bvb. 100 gastenboeken heb met elk 10000 berichten erin.
Daarom mijn vraag naar suggesties en opmerkingen. Op welke manier kan diet het beste opgelost, of liever, ontworpen worden?

[kenneth]

Dan heb je een tabel met een miljoen records. Peanuts voor een databaseserver. Zeker als je goede indexen op je tabel hebt staan.

[NMe]

Bovendien sla je niet "boek1" op. Je hebt een tabel "gastenboek" en een tabel "bericht" nodig. In de tabel "gastenboek" sla je dingen op als de naam van het gastenboek en eventueel wat stylinginformatie of wat dan ook dat specifiek is voor het gastenboek. In "bericht" sla je het bericht op inclusief IP-adres van de poster ervan, datum, tijd, enz. En natuurlijk een verwijzing; een foreign key die verwijst naar de gastenboektabel en aangeeft welk bericht bij welk gastenboek hoort.

[cariolive23]

code:

SELECT * FROM gastenboeken WHERE gastenboek = "boek2"

Volgens mij schrijft de SQL-standaard enkele quotes voor:

code:

SELECT * FROM gastenboeken WHERE gastenboek = 'boek2';

Oracle en PostgreSQL zullen bij dubbele quotes de content tussen deze quotes zien als een objectnaam, bijvoorbeeld een kolom- of tabelnaam (afhankelijk van de context).

MySQL staat beide toe, maar het is wel zo handig om een syntax te leren die ook op andere databases werkt.

Ps. Gebruik nooit een * maar schrijf altijd netjes de kolomnamen uit. Dit voorkomt een hoop bugs.

Edit: FireBird slikt dubbele quotes rondom een string evenmin, ze verwijzen zelfs naar de SQL-92 standaard waar e.e.a. over dubbele quotes wordt uitgelegd. Zie de handleiding. MySQL lijkt weer eens een rare uitzondering te zijn... What's new?

[ Voor 31% gewijzigd door cariolive23 op 11-08-2009 14:36 . Reden: url toegevoegd ]

[Cascade]

Je kan stilstaan bij de hoeveelheid berichten van een gastenboek die je in 1 keer laat zien: meteen alles of maar een deel, oftwel pagineren? _{*uche LIMIT}.

[Erkens]

Cascade schreef op dinsdag 11 augustus 2009 @ 14:49:
Je kan stilstaan bij de hoeveelheid berichten van een gastenboek die je in 1 keer laat zien: meteen alles of maar een deel, oftwel pagineren? _{*uche LIMIT}.

Zodra je dat wilt doen dan zal je ook iets als sortering moeten doen anders is de kans dat je "wazige" resultaten kan krijgen. Dus is het handig om bijvoorbeeld een datum/tijd mee op te slaan zoals NMe ook al aangaf.

[Hydra]

NMe schreef op dinsdag 11 augustus 2009 @ 13:50:
Bovendien sla je niet "boek1" op. Je hebt een tabel "gastenboek" en een tabel "bericht" nodig. In de tabel "gastenboek" sla je dingen op als de naam van het gastenboek en eventueel wat stylinginformatie of wat dan ook dat specifiek is voor het gastenboek. In "bericht" sla je het bericht op inclusief IP-adres van de poster ervan, datum, tijd, enz. En natuurlijk een verwijzing; een foreign key die verwijst naar de gastenboektabel en aangeeft welk bericht bij welk gastenboek hoort.

Inderdaad. En op die ID zit dus een index die ervoor zorgt dat als je alle berichten van gastenboek 1 ophaalt hij geen volledige tablescan hoeft te doen. Dat is, zoals gezegd, peanuts voor een fatsoenlijke DB.

[disjfa]

Hydra schreef op dinsdag 11 augustus 2009 @ 14:58:
[...]
Inderdaad. En op die ID zit dus een index die ervoor zorgt dat als je alle berichten van gastenboek 1 ophaalt hij geen volledige tablescan hoeft te doen. Dat is, zoals gezegd, peanuts voor een fatsoenlijke DB.

Je bedoelt "op dat ID moet je een index zetten" als de inhoud wat groot word.

[cariolive23]

Je moet een index zetten op die kolommen die je gebruikt bij het zoeken en sorteren. Daarnaast zijn er nog constraints zoals bijvoorbeeld een UNIQUE-constraint of een primary key. Daar worden indexen voor gebruikt, die krijg je er dus vanzelf bij.

Om te achterhalen hoe een database een query uitvoert, gebruik je EXPLAIN. Dat wordt bij mijn weten, door vele databases ondersteund. Met behulp van deze resultaten ga je uitzoeken op welke kolommen nu indexen moeten worden geplaatst. Lukraak indexen aanmaken kan juist voor slechtere resultaten zorgen.

[Hydra]

disjfa schreef op dinsdag 11 augustus 2009 @ 15:01:
Je bedoelt "op dat ID moet je een index zetten" als de inhoud wat groot word.

Nee, je zet een index op die kolommen waar je op zoekt. Punt. Kort door de bocht, maar hoe groot je DB is is geen argument. Je doet 't goed of je doet 't niet.

[ Voor 16% gewijzigd door Hydra op 11-08-2009 15:12 ]

[NMe]

Nouja, een index zetten op een tabel waarvan je weet dat het aantal records nooit het aantal vingers aan je handen zal overstijgen is redelijk pointless geloof ik. Maar het kan dan ook weer geen kwaad.

[GlowMouse]

Hydra schreef op dinsdag 11 augustus 2009 @ 15:11:
[...]


Nee, je zet een index op die kolommen waar je op zoekt. Punt. Kort door de bocht, maar hoe groot je DB is is geen argument. Je doet 't goed of je doet 't niet.

En op sorteert. Maar het goed zetten van indices vereist wel een dagje goed uitzoekwerk van hoe multi-column indices nou precies werken. Daarover is voldoende informatie te vinden, en het is ook wat veel om hier uit te typen.

Cascade schreef op dinsdag 11 augustus 2009 @ 14:49:
Je kan stilstaan bij de hoeveelheid berichten van een gastenboek die je in 1 keer laat zien: meteen alles of maar een deel, oftwel pagineren? _{*uche LIMIT}.

Pagineren met LIMIT is niet zo handig. Tenzij je een covering index gebruikt om snel de juiste rijen op te halen, wordt het enorm traag bij de laatste paar pagina's.

[ Voor 30% gewijzigd door GlowMouse op 12-08-2009 01:41 ]

[wackmaniac]

GlowMouse schreef op woensdag 12 augustus 2009 @ 01:39:
[...]

En op sorteert. Maar het goed zetten van indices vereist wel een dagje goed uitzoekwerk van hoe multi-column indices nou precies werken. Daarover is voldoende informatie te vinden, en het is ook wat veel om hier uit te typen.

[...]

Pagineren met LIMIT is niet zo handig. Tenzij je een covering index gebruikt om snel de juiste rijen op te halen, wordt het enorm traag bij de laatste paar pagina's.

Heb je daar een benchmark van? ALs ik een gastenboek heb met 10000 berichten en ik mag geen gebruik maken van LIMIT, dan moet ik 10000 berichten ophalen, daar objecten van maken en dat dan met bv PHP gaan opdelen in pagina's? Volgens mij is LIMIT nog steeds de way to go.

[Verwijderd]

GlowMouse schreef op woensdag 12 augustus 2009 @ 01:39:

[...]

Pagineren met LIMIT is niet zo handig. Tenzij je een covering index gebruikt om snel de juiste rijen op te halen, wordt het enorm traag bij de laatste paar pagina's.

Hoe dan wel?

[Voutloos]

MIsschien heb je ooit trucs nodig om queries met grote offsets te voorkomen, maar zorg nou maar eerst dat je geustbook uberhaupt zo populair wordt.

[Cascade]

GlowMouse schreef op woensdag 12 augustus 2009 @ 01:39:
[...]

Pagineren met LIMIT is niet zo handig. Tenzij je een covering index gebruikt om snel de juiste rijen op te halen, wordt het enorm traag bij de laatste paar pagina's.

LIMIT en OFFSET zijn hiervoor gemaakt, maar hebben inderdaad een redelijk vertraging voor grotere offsets, mede afhankelijk van welke data je ophaalt.

Bijvoorbeeld dit:

SQL:

SELECT * FROM bla AS bla_data
INNER JOIN (SELECT id FROM bla LIMIT 20000,20) AS bla_page
ON bla_data.id=bla_page.id;

Is sneller dan dit:

SQL:

SELECT * FROM bla AS bla_data LIMIT 20000,20;

Met id als primary key. Punt hier is dat je snelheid haalt door voor LIMIT alleen in een index te blijven voor zoeken, positioneren en ophalen van waardes. Ook iets om bij na te denken.

Maar de ordegrootte van vertragingen waar we het hier over hebben... volgens mij zal dat in het begin helemaal geen probleem worden. Daarnaast, als je kijkt naar de user experience... meestal zal je een gastenboek op chronologische volgorde sorteren, nieuwste eerst, het is dan IMHO acceptabel dat de bezoeker een fractie van een seconde langer moet wachten op oude berichten van pagina 5634 dan op de nieuwste berichten.

[ Voor 19% gewijzigd door Cascade op 12-08-2009 10:12 ]

[Erkens]

Cascade schreef op woensdag 12 augustus 2009 @ 10:00:
Bijvoorbeeld dit:

SQL:

SELECT * FROM bla AS bla_data
INNER JOIN (SELECT id FROM bla LIMIT 20000,20) AS bla_page
ON bla_data.id=bla_page.id;

Is sneller dan dit:

SQL:

SELECT * FROM bla AS bla_data LIMIT 20000,20;

Nee, die tweede is sneller, want diezelfde query heb je in die eerste als subquery staan, dus die kan _nooit_ sneller zijn Tevens moet je niet SELECT * doen bij die eerste query want dan krijg je alle kolommen dubbel.

offtopic:
verder moet je LIMIT nooit gebruiken zonder ORDER BY

[ Voor 8% gewijzigd door Erkens op 12-08-2009 10:11 ]

[Voutloos]

Erkens schreef op woensdag 12 augustus 2009 @ 10:10:
want diezelfde query heb je in die eerste als subquery staan, dus die kan _nooit_ sneller zijn

Niet waar, een dergelijk trucje kan wel degelijk een enorme positieve impact hebben. Het is echter wel flink stom dat je je query zo moet schrijven, maar dit is een van de optimalisaties welke in mysql 6.0 eindelijk logischerwijs vanzelf gebeurt.

Onderbouwing: De derived table kan in explain verschijnen met 'using index' (covering index dus), terwijl de buitenste query dat vaak niet kan. Maar goed, zorg eerst maar eens dat de omvang van de db dus een probleem wordt.

[ Voor 20% gewijzigd door Voutloos op 12-08-2009 10:16 ]

[Erkens]

Voutloos schreef op woensdag 12 augustus 2009 @ 10:15:
[...]
Niet waar, een dergelijk trucje kan wel degelijk een enorme positieve impact hebben. Het is echter wel flink stom dat je je query zo moet schrijven, maar dit is een van de optimalisaties welke in mysql 6.0 eindelijk logischerwijs vanzelf gebeurt.

O, dat het een positieve impact kan hebben ontken ik ook niet, maar die totale query kan nooit sneller zijn dan die losse "subquery"

[Cascade]

Erkens schreef op woensdag 12 augustus 2009 @ 10:10:
[...]

Nee, die tweede is sneller, want diezelfde query heb je in die eerste als subquery staan, dus die kan _nooit_ sneller zijn

Ik heb dit gemeten, die eerste is sneller. Je mist hier totaal het punt. Die eerste query haalt de id uit de index, niet uit de tabel zoals de tweede. Dat is een stuk sneller, ondanks de JOIN.

Tevens moet je niet SELECT * doen bij die eerste query want dan krijg je alle kolommen dubbel

offtopic:
verder moet je LIMIT nooit gebruiken zonder ORDER BY

Nee, dat is allebei niet zo.

[Voutloos]

Erkens schreef op woensdag 12 augustus 2009 @ 10:17:
[...]
O, dat het een positieve impact kan hebben ontken ik ook niet, maar die totale query kan nooit sneller zijn dan die losse "subquery"

Ja ok, maar het is juist het verschil dat je de subquery tot minder kolommen kan beperken en daardoor een covering index hebt.

Cascade schreef op woensdag 12 augustus 2009 @ 10:19:
Nee, dat is allebei niet zo.

Order by bij limit queries is inderdaad niet verplicht, tenzij je van deterministisch gedrag houdt. Als je limit, denk je per definitie aan een bepaalde volgorde en dat kan je dan maar beter expliciet aangeven.

[ Voor 70% gewijzigd door Voutloos op 12-08-2009 10:22 ]

[Yoozer]

Tino1986 schreef op dinsdag 11 augustus 2009 @ 13:46:
Dat werkt zo zonder probleem. Maar ik maak me (misschien onterecht) zorgen over de performantie van deze manier van aanpak. Wat als ik bvb. 100 gastenboeken heb met elk 10000 berichten erin.

Sta je gebruikers toe om hun berichten ten alle tijde te laten wijzigen? Wijzigt het aantal replies per pagina? Zoniet, keihard cachen die hap.

[Erkens]

Cascade schreef op woensdag 12 augustus 2009 @ 10:19:
Ik heb dit gemeten, die eerste is sneller. Je mist hier totaal het punt. Die eerste query haalt de id uit de index, niet uit de tabel zoals de tweede. Dat is een stuk sneller, ondanks de JOIN.

[...]

Nee, dat is allebei niet zo.

Zodra je ORDER BY gebruikt dan is dat vieze truukje met die JOIN niet eens nodig Overigens vraag ik me af waar we het over hebben, dit soort optimalisaties ga je pas aan beginnen als het nodig is, of als je op voorhand weet dat het een probleem gaat worden. Voor een simpel gastenboek lijkt me dat nog lang niet van toepassing.

[Voutloos]

Typisch got weer. De reply van Kenneth is gewoon al voldoende. Datamodel hintje van Nme mag er ook nog wel bij, maar daarna dwaalt het al gauw af naar mierenneuken en performance geklets terwijl TS in dit geval nog geen enkel reactie heeft opgeslagen.

offtopic:
En ook met order by kan je die truc nog nodig hebben.

[Marv]

Voutloos schreef op woensdag 12 augustus 2009 @ 10:44:
Typisch got weer. De reply van Kenneth is gewoon al voldoende. Datamodel hintje van Nme mag er ook nog wel bij, maar daarna dwaalt het al gauw af naar mierenneuken en performance geklets terwijl TS in dit geval nog geen enkel reactie heeft opgeslagen.

Ik vind het persoonlijk wel interessant, maar je hebt wel gelijk dat TS hier waarschijnlijk (op dit moment) niets aan heeft.

Misschien een idee voor een apart topic?

[Remus]

Cascade schreef op woensdag 12 augustus 2009 @ 10:00:
[...]


LIMIT en OFFSET zijn hiervoor gemaakt, maar hebben inderdaad een redelijk vertraging voor grotere offsets, mede afhankelijk van welke data je ophaalt.

Bijvoorbeeld dit:

SQL:

SELECT * FROM bla AS bla_data
INNER JOIN (SELECT id FROM bla LIMIT 20000,20) AS bla_page
ON bla_data.id=bla_page.id;

Is sneller dan dit:

SQL:

SELECT * FROM bla AS bla_data LIMIT 20000,20;

Met id als primary key. Punt hier is dat je snelheid haalt door voor LIMIT alleen in een index te blijven voor zoeken, positioneren en ophalen van waardes. Ook iets om bij na te denken.

Maar de ordegrootte van vertragingen waar we het hier over hebben... volgens mij zal dat in het begin helemaal geen probleem worden. Daarnaast, als je kijkt naar de user experience... meestal zal je een gastenboek op chronologische volgorde sorteren, nieuwste eerst, het is dan IMHO acceptabel dat de bezoeker een fractie van een seconde langer moet wachten op oude berichten van pagina 5634 dan op de nieuwste berichten.

Of dat een betere performance oplevert hangt heel erg af van je database systeem, dergelijke oplossingen moet je pas gaan uitproberen als 1) weet dat er een bottleneck zit en 2) een representatieve hoeveelheid data hebt om mee te testen.

[NMe]

Marv schreef op woensdag 12 augustus 2009 @ 10:55:
[...]

Ik vind het persoonlijk wel interessant, maar je hebt wel gelijk dat TS hier waarschijnlijk (op dit moment) niets aan heeft.

Misschien een idee voor een apart topic?

Laten we dat maar doen als jullie hierover verder willen gaan. De topicstarter heeft vast graag zijn topic terug.

Dus: als deze discussie verder gaat, bij dezen graag het verzoek om daar een nieuw topic voor te starten.

[GlowMouse]

Erkens schreef op woensdag 12 augustus 2009 @ 10:30:
[...]

Zodra je ORDER BY gebruikt dan is dat vieze truukje met die JOIN niet eens nodig Overigens vraag ik me af waar we het over hebben, dit soort optimalisaties ga je pas aan beginnen als het nodig is, of als je op voorhand weet dat het een probleem gaat worden. Voor een simpel gastenboek lijkt me dat nog lang niet van toepassing.

Zeker als je nog wat joins op andere tabellen hebt, gaat het verschil oplopen. Bij 100 pagina's pak je rustig een factor 10-100 winst als de data niet in het geheugen staat. En dan heb je het over tienden van seconden versus seconden.

NMe schreef op woensdag 12 augustus 2009 @ 13:15:
[...]

Laten we dat maar doen als jullie hierover verder willen gaan. De topicstarter heeft vast graag zijn topic terug.

Dus: als deze discussie verder gaat, bij dezen graag het verzoek om daar een nieuw topic voor te starten.

Eerst maar eens wachten op TS

[ Voor 21% gewijzigd door GlowMouse op 12-08-2009 13:16 ]

[Erkens]

GlowMouse schreef op woensdag 12 augustus 2009 @ 13:15:
Zeker als je nog wat joins op andere tabellen hebt, gaat het verschil oplopen. Bij 100 pagina's pak je rustig een factor 10-100 winst als de data niet in het geheugen staat. En dan heb je het over tienden van seconden versus seconden.

Je hoeft dat mij niet te vertellen hoor, ik had het puur over de hier gegeven voorbeelden...

[NMe]

GlowMouse schreef op woensdag 12 augustus 2009 @ 13:15:
[...]

Eerst maar eens wachten op TS

Vind je het heel gek dat die niet meer reageert nadat zijn vrij simpele vraag is ontaard in een discussie over micro-optimalisaties? Dus nogmaals: laten we die discussie ofwel afbreken ofwel in een eigen topic voortzetten.

[Tino1986]

Bedankt voor alle antwoorden iedereen. De eerste reply was inderdaad al voldoende, maar uit de anderen heb ik ook wel wat kunnen bijleren.
Ik had eerlijk gezegd maar 1 of 2 antwoorden verwacht. Was ik effe verbaasd met meer dan 20 op een dag tijd...
Bezige bende hier.

Bedankt.