I want to add a new row to my db table on page views when the user view a page.
This could easily be done by incrementing the number of views for a certain page in pages table.
However, I want to have more control over the click-spammers ;D. I mean, I would like to add a new row in my table called e.g. page_views_all only if there was no previous record with the same IP or the time of the previous record for the same page_id and the same IP was no less then 5 minutes. So, the same article view could be added only every 5 minutes and if the user with the same IP click on the same article (e.g. checking new comments and refreshing the page) many times, then this is not counted as a click and it's not added as a new row to the database.
And I was thinking about creating 3 tables and I would like to hear from you what do you think about my idea and how can I make it better.
So, I would like to have 3 tables:
1) page_views_all (for storing every page_view; except the same IP only every 5 minutes limit I was talking above)
2) page_views_grouped_by_page_id (CRON will run a script to check page_views_all table every 30 or 60 minutes and group all rows by id and count them and store number of page views for every page id; This way we can reduce the table size many many times, because we don't take users in count only pages)
3) page_views_by_day a simple table with only one record per day (run everyday at midnight via CRON; will be sorting all page views and number of unique visitors etc.for this day; expecting only 365 records a year)
And maybe a 4th table ;) page_views_domain (again by cron and store the total amount of page_views for all the time from the website start.
What do you think about this approach? Is there any simpler solution for MySQL? It's important that I want to save visitor's IP, user agent, time of the visit, page_id, maybe also previous_page_id(referrer). I am using Disqus and no logged in users in my system so storing a user_id is not happening in my case, just IP address.
The philosophy behind so many tables for storing page_views is that I want by website to be fast and by serving users with results from table 2 (page_views_grouped_by_page_id) or table 3 (page_views_by_day) and not from the huge table 1 I can speed up the website quite a bit, at least I think so.
One thing I am not sure of is, if making a query to the first and biggest table that stare every page_view via CRON will not slow down the website? If I will do it e.g. every 30 minutes or every 1 hour? What do you think? Will setting limit to last 10000 rows (depending on how many views will people make in 30 or 60 minutes ) and counting and grouping them will help. Because, after a time there could be millions of rows, if not billion. Who knows how popular the page will become ;). If this could help I was thinking about creating a dynamic variable in a separate table that would contain how many pages should be checked by cron, depending on previous results. But, I think I am getting too complicated here and I shouldn't worry about that right now ;).
PHP and setting CRON is not a problem I can programm all the functionality. The only concern of mine is how to structure the tables for storing the data.
ANOTHER BIG CONCERN OF MINE:
The checking process before inserting new row bothers me a little. Because I will need to check at least 1000 or more latest rows from the biggest table where all these page_views are stored. And I am not sure if this could not slow down my page, because on every page reload I will access this table and if I have 200 or more people online I don't know what can happen? Will it be OK? The pages are cached as html so the views are presented cached, but the checking in the db before the page output will still hit the db.
ONE GOOD THING THOUGH ;)
I need to make the db check/insert only for my articles (article detail page). I don't need to count homepage, categories or about us page visits. I can get that info from Google Analytics if I want to later. No need to have that info accurate for users every 30 or 60 minutes.