Until now, I stored all aggregate visits in the Pages table, and I also had other tables that held, for example, a user’s visits for individual days, or visits from a given IP regardless of whether the user was logged in – also broken down by day. However, this was getting slower and slower, even though the visit tables were still almost empty, with only about 1,500 records.
The cause was that each page was identified by its own address, and having to search first in the PagesArgument table and then in PagesNames, and then use the IDs found there to look up the page ID in Pages before working with it further, all of that slowed things down quite a bit. That is why I created a Pages table that contains all page IDs, along with whether comments can be added to them + whether more information is held in the Pages1 or Pages2 table, and then the Pages1 and Pages2 tables themselves.
Pages1 is almost the same as the previous Pages, except that it does not contain the column saying whether comments can be added to the page. I now store in it all pages that have no query string, which means that even pages like RIP – this server did not even survive its own hosting. 8 Metro Controls, where the parameters are handled through a “pretty” URI. Pages2, just like Pages1, contains the page ID that is in Pages, plus the ID of the entity group and of the entity itself. As a result, it is now much faster to get the total number of visits for pages with a variable parameter in the URI – all it takes is a query to the DB with the entity group ID and the entity ID. The entity ID can only be an Int or a smaller numeric data type.
I did all of this, meaning the Pages, Pages1 and Pages2 tables, so that I could use unified code for comments on my own website, which determines whether comments can be generated for a page simply by whether that page exists in the table.
EDIT The day after, January 24, 2015: It is now finally possible to add comments even on pages with dynamic parameters. Check it out, for example, on this photo – the link leads into the void, quite literally.. Of course, to add comments, you must be logged in.