[SQL] Efficient bezoekersstatistieken opslaan

[Reveller]

Ik wil zo efficient mogelijk het aantal hits op mijn site opslaan in een mysql database en deze beschikbaar maken voor verschillende grafieken. Momenteel heb ik de volgende tabel (stats_hits) daarvoor verzonnen:

code:

+------------+------+------+------+------+------+
| date       | h1   | h2   | ...  | h23  | h24  |
+------------+------+------+------+------+------+
| 2006-04-22 | 432  | 543  | ...  | 123  | 32   |
+------------+------+------+------+------+------+
| 2006-04-23 | 541  | 644  | ...  | 321  | 144  |
+------------+------+------+------+------+------+

Met andere woorden: de tabel bestaat uit 25 kolommen. De eerste bevat de datum en de overige 24 het aantal hits in elk uur van de dag. De 432 in de eerste recordset betekent "432 hits tussen 01:00 en 01:59 in de ochtend van 22 april 2006". Op deze manier kan ik gedetailleerde informatie over het aantal hits (=pageviews) opslaan terwijl de database maar met 365 recordsets per jaar groeit

Ik dacht nu met een aantal eenvoudige queries informatie te kunnen ophalen over bijvoorbeeld het aantal pageviews:

• op 22 april, verdeeld over uren van de dag
• op 22 en 23 april (gemiddeld), verdeeld over uren van de dag
• aantal pageviews in april, verdeeld over de dagen van april
• aantal pageviews in 2006, verdeeld over maanden

Dit blijkt alleen veel minder makkelijk, omdat ik in elke query expliciet de kolommen 1 t/m 24 moet aanroepen, voordat ik een totale SUM kan uitvoeren (bijvoorbeeld totaal aantal hits in april ophalen):

SQL:

SELECT SUM(h1) + SUM(h2) + SUM(h3) ... + SUM(h24)
WHERE MONTH(date) = 4 AND YEAR(date) = 2006

Vandaar twee vragen:

• hoe goed / slecht is deze tabel werkelijk?
• kan ik niet op een efficientere manier de gevraagde gegevens oproepen? Ik heb gezocht op MySQL.com, maar kon geen functie vinden die werkt in de zin van "SELECT SUM(1 to 24) FROM ..."

[Sybr_E-N]

Een tabel, waar je voor elk uur een speciale kolom hebt is over het algemeen nou niet het meest slimme oplossing.

De eerste vraag/opmerking die mij naar boven borrelt: waar is je genormaliseerde database ontwerp?

[Mithrandir]

Maakt het werkelijk uit of je tabel 24 keer zoveel kolommen heeft?

24*365 is nog geen 9000 records. Zelfs na 10 jaar is dat nog peanuts. Waarom optimaliseer je dit al voordat 't überhaupt een probleem wordt?

[Verwijderd]

Een pagehit is wat mij betreft een 'gebruiker' op een 'tijdstip' op een bepaalde 'pagina'. Volgens mij moet een tabel er dus ook zo uit zien. De statistieken per uur is wat mij betreft query werk, geen database ontwerp.

[Reveller]

Mithrandir schreef op zaterdag 15 april 2006 @ 12:34:
Maakt het werkelijk uit of je tabel 24 keer zoveel kolommen heeft?

24*365 is nog geen 9000 records. Zelfs na 10 jaar is dat nog peanuts. Waarom optimaliseer je dit al voordat 't überhaupt een probleem wordt?

Dus dan wordt het zoiets?

code:

+------------+------+------+
| date       | hour | hits |
+------------+------+------+
| 2006-04-22 | 1    | 543  |
+------------+------+------+
| 2006-04-22 | 2    | 644  |
+------------+------+------+
| 2006-04-22 | ...  | ...  |
+------------+------+------+
| 2006-04-22 | 24   | 434  |
+------------+------+------+
| 2006-04-23 | 1    | 123  |
+------------+------+------+

Verwijderd schreef op zaterdag 15 april 2006 @ 13:20:
Een pagehit is wat mij betreft een 'gebruiker' op een 'tijdstip' op een bepaalde 'pagina'. Volgens mij moet een tabel er dus ook zo uit zien. De statistieken per uur is wat mij betreft query werk, geen database ontwerp.

Ik weet niet of ik begrijp wat je bedoelt, maar ik ga niet voor elke hit de gebruiker (sessie oid) en de pagina opslaan. Dat doe ik alleen voor de bezoeken van de laatste drie maanden. Alle gedetailleerde bezoekersdata ouder dan drie maanden gooi ik weg. Wel bewaar ik gegevens over bezochte pagina's (hits) per dag.

[ Voor 3% gewijzigd door Reveller op 15-04-2006 22:49 ]

[eamelink]

Wat bedoel je uberhaupt met "zo efficiënt mogelijk"? Geheugen efficiënt? Processor efficiënt? Diskruimte efficiënt? Minimale codingtijd-efficiënt?

Ben je écht opzoek naar de meest efficiënte manier (welke dat ook moge zijn), of zoek je gewoon naar een goede / handige / best-practice manier?

[OnTracK]

Ik ben het eens met mark platvoet, gewoon iedere hit een id en een timestamp geven (evt pagina, host e.d.), en dan kan je met een query de statistieken genereren. Nu "genereer" je deze al iedere keer als er een hit plaatsvindt, en dat lijkt me sowieso al niet erg efficiënt.

Dus:

code:

+----+---------------------+
| id | timestamp           |
+----+---------------------+
| 50 | 2006-04-22 15:55:36 |
+----+---------------------+
| 51 | 2006-04-22 15:56:12 |
+----+---------------------+
| 52 | 2006-04-22 15:56:41 |
+----+---------------------+

Dan kan je bovendien later ook nog eens ándere statistieken eruittrekken als je wil

[ Voor 9% gewijzigd door OnTracK op 15-04-2006 23:05 ]

[Wacky]

Bovenstaande lijkt mij ook het beste, en dan iedere dag/week/maand statistieken van de afgelopen dag/week/maand genereren (de "hits" records kunnen dan verwijdert worden waardoor je database ook niet zo groot wordt).

[Reveller]

eamelink schreef op zaterdag 15 april 2006 @ 22:53:
Wat bedoel je uberhaupt met "zo efficiënt mogelijk"?

Efficient:

• qua schijfruimte, dus zoveel mogelijk data opslaan terwijl de database in omvang relatief klein blijft
• qua processorkracht, dus informatie moet al wat "voorbewerkt" zijn in de database zodat geen queries van 4 minuten nodig zijn om bv. het aantal hits over dagen van de week uit te rekenen

De reden hiervoor is dat ik dit statistiekentooltje wil verwerken in een CMS'je dat ik wil verkopen. De meeste klanten zullen op een shared (want: goedkoper) hosting accountje gaan draaien, waar de harde schijfruimte en processorkracht (want: gedeeld) beperkt is.

OnTracK schreef op zaterdag 15 april 2006 @ 23:04:
Ik ben het eens met mark platvoet, gewoon iedere hit een id en een timestamp geven (evt pagina, host e.d.), en dan kan je met een query de statistieken genereren.

Dat doe ik al (in een tabel genaamd stats_paths), maar om ruimte te besparen worden deze gedetailleerde gegevens elke drie maanden weg gegooid. De belanrijkste info uit die tabel, het aantal hits op een moment in de tijd, wil ik voor altijd bewaren. Vandaar een aparte tabel. De rest van de info, bijvoorbeeld dat sessie c4deaf098102151d5844af7af1738d35, vier jaar geleden een keer op pagina producten/groenten/winter is geweest, vind ik niet zo interessant. Die bezoekersdata mag na drie maanden weg. De populariteit van individuele pagina's wordt in een aparte tabel bijgehouden, zodat ook die data niet verloren gaat. Voor de volledigheid: het voorlopige database ontwerp staat hier (tabellen zijn nog niet verbonden).

Nu "genereer" je deze al iedere keer als er een hit plaatsvindt, en dat lijkt me sowieso al niet erg efficiënt. Dan kan je bovendien later ook nog eens ándere statistieken eruittrekken als je wil

Die andere statistieken haal ik uit andere tabellen (zie ontwerp). Gezien de eerdere opmerking over processorkracht is dit juist erg efficient

Wacky schreef op zondag 16 april 2006 @ 00:08:
Bovenstaande lijkt mij ook het beste, en dan iedere dag/week/maand statistieken van de afgelopen dag/week/maand genereren (de "hits" records kunnen dan verwijdert worden waardoor je database ook niet zo groot wordt).

Dat is dus eigenlijk wat ik al doe (zie db ontwerp) of praten we nu toch nog over iets anders?

[eamelink]

Reveller schreef op zondag 16 april 2006 @ 14:48:
Efficient:

• qua schijfruimte, dus zoveel mogelijk data opslaan terwijl de database in omvang relatief klein blijft
• qua processorkracht, dus informatie moet al wat "voorbewerkt" zijn in de database zodat geen queries van 4 minuten nodig zijn om bv. het aantal hits over dagen van de week uit te rekenen

De reden hiervoor is dat ik dit statistiekentooltje wil verwerken in een CMS'je dat ik wil verkopen. De meeste klanten zullen op een shared (want: goedkoper) hosting accountje gaan draaien, waar de harde schijfruimte en processorkracht (want: gedeeld) beperkt is.

Okee, dus in principe is het belangrijkste dat het niet teveel schijfruimte inneemt, en dat het niet teveel processorkracht trekt. Voor het genereren van de statistieken zelf (mbv een cronjob ofzo), mag dat waarschijnlijk wel iets meer zijn.

Voor de volledigheid: het voorlopige database ontwerp staat hier (tabellen zijn nog niet verbonden).

Het eerste wat mij opvalt is dat je aparte tabellen hebt voor IP's, cookies en sessions, terwijl die eigenlijk alledrie bedoeld zijn om dezelfde gebruiker te identificeren. Ik zou kijken of je op één of andere manier daarvoor een tabel users kan maken, waarin je dat allemaal kwijt kan. In principe wil je natuurlijk weten hoeveel mensen er komen, hoevaak ze terug komen, hoeveel unieke bezoeker je hebt en dergelijke, en niet hoeveel cookies of sessions er zijn of waren...

Het leuke is dat je dat dan ook kan koppelen aan de stats_paths tabel. Nu is het nog een tamelijk abstracte tabel waar je niet veel meer uit kan halen dan 'Verreweg de meeste mensen op de 'about' pagina komen vanaf de 'contact' pagina. Je hebt niet écht een beeld hoe de gebruikers gaan op deze manier

Dat is dus eigenlijk wat ik al doe (zie db ontwerp) of praten we nu toch nog over iets anders?

Je doet wel min of meer hetzelfde, maar toch ook niet echt. Wat bijvoorbeeld belangrijk is om te weten, is wanneer je de diverse tabellen bijwerkt. Bijvoorbeeld de tabel stats_datetime. Wanneer wordt die geupdate? Bij elk bezoek? Of destilleer je die uit andere tabellen?

Wat ik persoonlijk zou doen is de drie user-tabellen ips/cookies/sessions samenvoegen tot één tabel, en ervoor zorgen dat je zo geavanceerd mogelijk je users uit elkaar kan houden, en dan in de tabel stats_paths ook het user-id toevoegen.

[Reveller]

eamelink schreef op zondag 16 april 2006 @ 16:00:
[...]
Wat ik persoonlijk zou doen is de drie user-tabellen ips/cookies/sessions samenvoegen tot één tabel, en ervoor zorgen dat je zo geavanceerd mogelijk je users uit elkaar kan houden, en dan in de tabel stats_paths ook het user-id toevoegen.

Ik heb het database ontwerp aangepast (vorige ontwerp staat hier). De tabel stats_visits beschrijft nu per record de sessie, cookie en het IP van de user. Via VID (visitor ID) kan ik een koppeling maken met stats_paths, en zo zien hoe de bezoeker door de site loopt. Als iemand nog op- of aanmerkingen heeft op dit ontwerp, hoor ik het graag! Liever nu dan als het al gebouwd is

[CrashOne]

stats_browsers is totaal niet intressant zonder een datum zodat je kan zien of er verschuiving in de gebruikte browsers plaats vind. Anders gaat IE nu een groot percentage op bouwen maar zal het verschrikkelijk veel langer duren voor nieuwe en opkomende browsers om iets in je stats te betekenen.

Zelfde geldt alle andere tabellen (maar misschien zijn die gekoppelt aan stats_datetime, maar dat kan ik zo niet zien, dus post even hoe je relaties liggen).

[Reveller]

CrashOne schreef op dinsdag 18 april 2006 @ 21:39:
stats_browsers is totaal niet intressant zonder een datum zodat je kan zien of er verschuiving in de gebruikte browsers plaats vind. Anders gaat IE nu een groot percentage op bouwen maar zal het verschrikkelijk veel langer duren voor nieuwe en opkomende browsers om iets in je stats te betekenen.

Daar heb je gelijk in, maar ik weet niet hoe ik bijvoorbeeld per week een overzicht van de verschillende gebruikte browsers zou kunnen maken. hoe zou zo'n tabel eruit moeten zien? Het probleem zit hem in het feit dat gebruikers de useragents in stats_browsers kunnen uitbreiden. Hoe koppel ik datum-hits informatie dan aan de verschillende browsers? Een idee dat ik heb, is door een extra tabel toe te voegen aan onderstaande database genaamd stats_browsers_date:

code:

+------------+------------+------+
| date       | browser_id | hits |
+------------+------------+------+
| 2006-04-22 | 1          | 43   |
+------------+------------+------+
| 2006-04-22 | 2          | 54   |
+------------+------------+------+

En hetzelfde zou ik dan kunnen doen voor stats_os en stats_resoluties. Ik heb geen technische achtergrond, maar heb naar beste kunnen met visio de relaties van de database aangegeven. Graag commentaar

[ Voor 124% gewijzigd door Reveller op 20-04-2006 18:59 ]

[Reveller]

* kick *

Ik heb de database behoorlijk vereenvoudigd:



In stats_pagehits worden de hits (=pageviews) per pagina van de website opgeslagen. In stats_datetime wordt per uur van de dag opgeslagen hoeveel unieke sessies, ips, cookies en pageviews er in dat uur waren. In stats_visits worden de unieke bezoeken van de afgelopen drie maanden opgeslagen. Via visit_id is deze tabel verbonden met stats_paths, waar van elk bezoek het klikpad wordt opgeslagen. In stats_tech wordt opgeslagen welke browsers, besturingssystemen en resoluties gemeten worden, bijvoorbeeld:

code:

+---------+-----------+---------------------+
| tech_id | type      | name                |
+---------+-----------+---------------------+
|       1 | browser   | Internet Explorer 5 |
+---------+-----------+---------------------+
|       2 | browser   | Mozilla Firefox 1.0 |
+---------+-----------+---------------------+
|       3 | resolutie | 1400 x 1200         |
+---------+-----------+---------------------+
|       4 | os        | Windows xP          |
+---------+-----------+---------------------+
|       5 | browser   | Konqueror           |
+---------+-----------+---------------------+

Via een admin interface moeten gebruikers nieuwe zoekstrings aan deze tabel kunnen toevoegen. In stats_techhits wordt voor elke tech_id per week bijgehouden hoeveel hits er daarmee waren, bijvoorbeeld 342 hits met Windows XP in week 42 van 2006.

Mijn ervaring met het ontwerpen van een database is niet groot, in de loop van dit topic heb ik hem al regematig aangepast. Wie heeft er nog op- of aanmerkingen, tips of ronduit (opbouwende ) kitiek? Ik hoor het graag! Bijvoorbeeld: voorzie je problemen als ik de statistieken zou willen uitbreiden, zie ik zaken over het hoofd (bijvoorbeeld relaties die niet kunnen of ontbreken, etc)?

[CH4OS]

Reveller schreef op zaterdag 22 april 2006 @ 17:22:
Via een admin interface moeten gebruikers nieuwe zoekstrings aan deze tabel kunnen toevoegen. In stats_techhits wordt voor elke tech_id per week bijgehouden hoeveel hits er daarmee waren, bijvoorbeeld 342 hits met Windows XP in week 42 van 2006.

Hoe wil je dan een jaar overzicht maken van de statistieken, als je na 3 maanden de DB opschoont?

Mijn ervaring met het ontwerpen van een database is niet groot, in de loop van dit topic heb ik hem al regematig aangepast. Wie heeft er nog op- of aanmerkingen, tips of ronduit (opbouwende ) kitiek? Ik hoor het graag! Bijvoorbeeld: voorzie je problemen als ik de statistieken zou willen uitbreiden, zie ik zaken over het hoofd (bijvoorbeeld relaties die niet kunnen of ontbreken, etc)?

Over normaliseren is een boel te vinden. Niet alleen in boeken, maar ook bijvoorbeeld bij Google:
[google=normaliseren database]
[google=normalise database]

[Verwijderd]

GJ-tje schreef op zaterdag 22 april 2006 @ 23:54:
[...]
Over normaliseren is een boel te vinden. Niet alleen in boeken, maar ook bijvoorbeeld bij Google:
[google=normaliseren database]
[google=normalise database]

zoeken bij google naar 'normalize database'

[Reveller]

GJ-tje schreef op zaterdag 22 april 2006 @ 23:54:
[...]
Hoe wil je dan een jaar overzicht maken van de statistieken, als je na 3 maanden de DB opschoont?

Alleen stats_visits en stats_paths worden elke drie maanden opgeschoond. De andere tabellen worden niet geleegd. Gedetailleerde informatie over een bezoek (zoals klikpad van een bezoeker of aantal bezoeken van een specifieke bezoeker) verdwijnt dus elke drie maanden, maar algemene informatie zoals aantal bezoekers per dag, blijft in de database staan in stats_datetime.

Over normaliseren is een boel te vinden. Niet alleen in boeken, maar ook bijvoorbeeld bij Google:
[google=normaliseren database]
[google=normalise database]

Had ik zelf ook al gevonden, en ook al redelijk wat over gelezen. Ik ben dus enigszins bekend met normaliseren, maar normaliseren staat niet gelijk aan ontwerpen. En de vraag waar het mij in dit topic over gaat is eigenlijk of er, om goede statistieken te draaien, data verzameld moet worden die ik hier vergeet, of dat er een efficientere manier is om bepaalde data op te slaan. Dat staat niet altijd gelijk aan to-the-max normaliseren, imho

[Reveller]

* kick * Graag jullie mening over het database model hierboven

[joopst]

Je datamodel is goed als je er alle statistieken die je wilt er weer uit kan halen.

Welke statistieken wil je eruit kunnen halen ?

Je weet toch wat ooit een goeie ontwerper heeft gezegd: Premature optimization is the root of all evil.

[ Voor 45% gewijzigd door joopst op 24-04-2006 13:28 ]

[PipoDeClown]

wil je de statistieken live kunnen tonen of mag er om x tijd een analyse gemaakt worden?

in eerste geval kan het dan voordelig zijn om je gegevens enigszins al te sorteren en waarderen voordat je ze in de db stopt.

in tweede geval plemp je zoveel mogelijk gegevens per hit in de database. daarvoor is denk ik een enkele tabel voldoende.

[Reveller]

PipoDeClown schreef op maandag 24 april 2006 @ 15:14:
wil je de statistieken live kunnen tonen of mag er om x tijd een analyse gemaakt worden?

Live tonen Deze statistieken zullen onderdeel gaan uitmaken van een eenvoudig cms dat ws. in 90% van de gevallen op een shared hosting accountje zal staan. Vandaar dat de statistieken al enigszins voorbewerkt opgeslagen moeten worden, immers: opslagruimte en processorkracht zijn beperkt.
[/quote]

joopst schreef op maandag 24 april 2006 @ 13:25:
Je weet toch wat ooit een goeie ontwerper heeft gezegd: Premature optimization is the root of all evil.

Wow Genoeg getwijfeld - lekker bouwen nu!

[joopst]

happy programming!