Mariadb – How to keep records of past week visits in a web app

change-trackingdatabase-designmariadb

I am developing a newspaper-like MVC webapp with an article content type which has a visits field. The database is MariaDB.

Each time a visitor requests an article, the visits field of the article row is incremented by 1. So I can easily query articles by their overall visits.

But what I want is to sort out articles based on number of visits which each article has got in the past week.

I thought to have a separate table like weekly-stats in which I can insert and increment article info with their recent visits upon each request, but then I need to empty the table weekly as a cron job, which is not ideal, and it leaves the stats empty after the data is being purged.

I also thought of having articles' visits saved in an expiring cache like redis and set the cached records to expire after a week, but then it brings all the complexities of inserting and querying redis hash elements.

So I thought there might be some more straightforward design strategies that I miss. How can I achieve this with the least hassle?

Best Answer

Each time a visitor requests an article, the visits field of the article row is incremented by 1. So I can easily query articles by their overall visits. [...] But what I want is to sort out articles based on number of visits which each article has got in the past week.

You can do it that way. I would record the visitors id though, unless you're getting too much traffic to do this.

CREATE TABLE article_visits (
  article_id  int REFERENCES article,
  user_id     int REFERENCES users,
  ts_visit    timestamp with time zone DEFAULT now()
);
CREATE INDEX ON article_visits ( article_id, ts_visit );

SELECT article_id, count(*)
FROM article AS a
LEFT OUTER JOIN article_visits USING (article_id)
WHERE ts_visit > now() - interval '1 week' -- PostgreSQL has intervals.
GROUP BY article_id;

I would use PostgreSQL. On MySQL you'll have to use their goofy proprietary DATE_SUB(), and WEEKOFYEAR(). Who knows what they're thinking here. They hate standards.

Related Question