1

I am planning out a mysql table to record page views. None of my options seem very efficient. I plan to have the pages send a +1 update to the table when loaded. What I want to be able to do is query the table for view count for the last 30 days, or a different query for view count last seven days. The best plan I can come up with is to insert a new record for each view with a date/time stamp. This would require that each of the two possible queries (30 days or 7 days) to scan the whole table and use a conditional clause. I would also need to remove rows older than 30 days old with a cron job or something. It seems that there should be a much more efficient way to do this. I use freelancers to do my work and have learned from past experience that I need to be specific in how I want things done. If you can suggest a better way to accomplish what I need in a more efficient way than I described please do so.

  • Can you add what you have done this far? What I'm seeing this is a simple table with yourPK and datetime growing vertically. – oNare Jul 16 '15 at 14:28
  • Do you have to build anything? This is the sort of thing I would normally do using Google Analytics, or in a bygone era myself using a HTTP log analyzer. – Colin 't Hart Jul 16 '15 at 14:35
1

I suggest you create a table with the following fields:

  • Page URL
  • Date (Not datetime)
  • Count

And add a unique key on (page_url, date).

Then, use INSERT INTO .. ON DUPLICATE KEY UPDATE.

So,

CREATE TABLE `page_view` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `page_url` varchar(512) NOT NULL,
  `date` date NOT NULL,
  `count` int(10) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `page_url` (`page_url`,`date`)
)

Then, use this insert:

INSERT INTO page_view (page_url, date) 
VALUES ('www.example.com/page1.html', date(now())) 
ON DUPLICATE KEY UPDATE count=count+1;
  • if you want to view total views per date frame regardless of the page URL itself, you may want to add an index on date field – Jehad Keriaki Jul 16 '15 at 14:49
  • Jihad, thanks for the response. The table structure and insert statement seem to be what I am looking for. The part about total page views per date frame regardless of the page URL confuses me. I will have about 10 pages that I will be tracking. I want to be able to run one of two queries on this table. One query will be asking for the view count of one specific page over the past 30 days, and the other query will be for the view count of a specific page over the past 7 days. Please clarify what you meant. Thanks, Ben – Ben Giles Jul 16 '15 at 15:22
  • If so, then you don't need that extra index. To clarify what I meant, this index would only be needed if you run this query: SELECT SUM(count) FROM page_view WHERE date>'2015-07-01'. – Jehad Keriaki Jul 16 '15 at 15:32
  • Jihad, now that I've had the time to consider your answer it seems to me that the date for each tracked page would be the the most recent update date. I don't see how it would be possible to get the number of views from the past x number of days if there is only one date there. – Ben Giles Jul 16 '15 at 16:24
  • Sorry about the name spelling. Must be the auto correct. – Ben Giles Jul 16 '15 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.