Once again I am doing what I want rather than what matters. I wanted to see how many people visit, say, the main Apps page and how many visit Lyrics, but I was only storing daily views for pages that have a row in the database. Here is how it works. All the table names I list here are made up, but they really exist. The foundation consists of 5 tables:
- VsechnyStranky – contains all pages – the ID of each one and its total view count
- StrankyDbEntity – contains a table ID column, the entity ID and the view count for each day
- Stranky – contains an index from NazvyStranek and QsArgumenty and the view count for each day
- QsArgumenty – because the database should be atomic and I want to save space, the arguments and the page names are kept in one table
- NazvyStranek – same as above
When any page is loaded that has not been loaded yet that day, it is saved to the VsechnyStranky table and then, depending on its type, either to StrankyDbEntity or to Stranky. The highest view count reached for the page is looked up in VsechnyStranky and stored in a new VsechnyStranky row – either 1 higher if the admin is not logged in, or unchanged. Rows a week old or older are deleted from the first 3 tables – after a week they are useless. This way I can quickly get the total view count across all days, today’s view count and the view count for the last 7 days (which is stored directly in the entity row and is more expensive to compute – so I only add it to tables where it will really make sense). So far it has 2 unfinished parts:
- It does not delete pages from the Stranky table (that is, the ones that are “not” dynamic)
- The chart for the last 7 days does not work – I will keep working on that.
PS: For now it is fully implemented only for the albums on the Photos site – everything else will follow soon.