MGCJerry
Limp Gawd
- Joined
- Sep 18, 2005
- Messages
- 144
The place I used to post my code questions has degraded a lot, thus I'm going to try [H] and see how it goes. I've had lots of good advice from here. I've typically bounced my ideas off others for ideas to get an overall input of what others think.
I've come from being a long time PHPNuke user (groan), and have learned what ideas 'work', and what doesn't and how NOT to do certain things (seriously, writing a search function for each and every module? Write one reusable function, or make it into a class and be done with it)... As a result I've setup a few of my sites using a lightweight in-house CMS which has a basic page view counter ('blah' page has been viewed 150 times). I have been entertaining the idea to expand on the view counter to give me stats down to the day & hour. I like what PHPNuke had for its stat breakdown, but to have 4 tables, and run 4 queries to update these tables is... Well not my idea of lightweight.
Here's what I have in mind but it utilizes one table, & 2 queries per page view. The site is already at 16 queries but one could be removed with this "new" stats idea so I would only be up to 17 queries per page load (some pages have more depending on which site)... Memory usage is also in the 1.5-1.6MB average range (which to me, seems high but it does make sense with a few things I have going on)
Proposed Structure:
table name 'views'
'stid' (primary key, auto increment),
'statdate' (datestamp)
'h1' (int8) unsigned,
'h2' (int8) unsigned,
'h3' (int8) unsigned,
... etc up to h24.
* A query to check for a row that is the current date. If no row exists, create one for the current date.
* UPDATE the row for the current hour (1AM will update 'h1+1', 13:36 will update 'h13+1', etc).
This will allow me to pull stats for a specific day and see an hourly breakdown without some stupid logic needed to parse through the data.
My original idea was to insert a new row every hour, but after a year of stats I would have 8,760 rows! 'nuke_stats_hour' from my old nuke site is 100,728 rows! (2002-2015). My new idea would only be 365 rows per year or 4,745 for 13 years of 'stats'.
I know there are plenty of good already made scripts. I know there is Google Analytics. Why do I want to reinvent the wheel?... Plenty of reasons. Because I'm old school (*shakes walker* get off my lawn & my lan). Because I don't want bloat. Because I want simplicity, its king. Because function>form. Because programming this is trivial. Because I like programming. Because like the rest of the site (minus a couple 3rd party extras), I want it to be mine to do with as I please.
So does this sound like a solid, simple plan? I'm quite open to an alternate way of doing things or other suggestions.
Edit:
I went ahead and done this, and as a test, I populated my views table with 51,000 records and did a number of tests speed tests. The speed was what I found acceptable according to HeidiSQL along with using 'explain' to optimize the queries I could use. I did have to add an index to improve handling when looking for stats for specific items.
Another change I did was create a field for the 'page'. This way I can see what pages are seen on what times & dates. I'll be removing the page counter from the content table, and relying on this way of handling stats. I will be able to remove 2 old queries because they are now made redundant by this change.
I've come from being a long time PHPNuke user (groan), and have learned what ideas 'work', and what doesn't and how NOT to do certain things (seriously, writing a search function for each and every module? Write one reusable function, or make it into a class and be done with it)... As a result I've setup a few of my sites using a lightweight in-house CMS which has a basic page view counter ('blah' page has been viewed 150 times). I have been entertaining the idea to expand on the view counter to give me stats down to the day & hour. I like what PHPNuke had for its stat breakdown, but to have 4 tables, and run 4 queries to update these tables is... Well not my idea of lightweight.
Here's what I have in mind but it utilizes one table, & 2 queries per page view. The site is already at 16 queries but one could be removed with this "new" stats idea so I would only be up to 17 queries per page load (some pages have more depending on which site)... Memory usage is also in the 1.5-1.6MB average range (which to me, seems high but it does make sense with a few things I have going on)
Proposed Structure:
table name 'views'
'stid' (primary key, auto increment),
'statdate' (datestamp)
'h1' (int8) unsigned,
'h2' (int8) unsigned,
'h3' (int8) unsigned,
... etc up to h24.
* A query to check for a row that is the current date. If no row exists, create one for the current date.
* UPDATE the row for the current hour (1AM will update 'h1+1', 13:36 will update 'h13+1', etc).
This will allow me to pull stats for a specific day and see an hourly breakdown without some stupid logic needed to parse through the data.
My original idea was to insert a new row every hour, but after a year of stats I would have 8,760 rows! 'nuke_stats_hour' from my old nuke site is 100,728 rows! (2002-2015). My new idea would only be 365 rows per year or 4,745 for 13 years of 'stats'.
I know there are plenty of good already made scripts. I know there is Google Analytics. Why do I want to reinvent the wheel?... Plenty of reasons. Because I'm old school (*shakes walker* get off my lawn & my lan). Because I don't want bloat. Because I want simplicity, its king. Because function>form. Because programming this is trivial. Because I like programming. Because like the rest of the site (minus a couple 3rd party extras), I want it to be mine to do with as I please.
So does this sound like a solid, simple plan? I'm quite open to an alternate way of doing things or other suggestions.
Edit:
I went ahead and done this, and as a test, I populated my views table with 51,000 records and did a number of tests speed tests. The speed was what I found acceptable according to HeidiSQL along with using 'explain' to optimize the queries I could use. I did have to add an index to improve handling when looking for stats for specific items.
Another change I did was create a field for the 'page'. This way I can see what pages are seen on what times & dates. I'll be removing the page counter from the content table, and relying on this way of handling stats. I will be able to remove 2 old queries because they are now made redundant by this change.
Last edited: