After a month ends, compact database to only include per-month views

[m417z]

After about four years of usage, the wordpress-popular-posts table on my blog is huge, and contains a level of details I don't need. Instead of having every visit logged, I'd be OK with having a per-month amount of views, but I don't want to lose the information completely.

Currently, I can do it manually with the following queries:

INSERT INTO `wp_popularpostssummary_by_month` (postid, pageviews, view_month, view_year)
SELECT postid, sum(pageviews), MONTH(view_date), YEAR(view_date)
FROM `wp_popularpostssummary`
WHERE YEAR(view_date) <= 2017
GROUP BY postid, MONTH(view_date), YEAR(view_date)

DELETE FROM `wp_popularpostssummary`
WHERE YEAR(view_date) <= 2017

In addition, I see that the pageviews value is always 1 in resent years, while it was non-zero in previous years. Looks like a recent update of the plugin started logging every visit, instead of grouping all visits per day. Is there any good reason to actually log each and every visit?

[m417z]

Actually, I fixed the "pageviews value is always 1" issue by adding a new UNIQUE key to the table, which is defined by a tuple of postid and view_date. To migrate my old data I did the following:

INSERT INTO `wp_popularpostssummary_temp` (postid, pageviews, view_date, view_datetime)
SELECT postid, sum(pageviews), view_date, max(view_datetime)
FROM `wp_popularpostssummary`
GROUP BY postid, view_date

RENAME TABLE `wp_popularpostssummary` TO `wp_popularpostssummary_old`

RENAME TABLE `wp_popularpostssummary_temp` TO `wp_popularpostssummary`

[cabrerahector]

Hi there!

Instead of having every visit logged, I'd be OK with having a per-month amount of views, but I don't want to lose the information completely.

You could set up a cronjob or a WP Cron Event to run the first query automatically for you.

About the second query, you can have WPP delete data older than N days automatically.

Is there any good reason to actually log each and every visit?

Yep, there is: WordPress Popular Posts 4.0 is finally out! (skip to Custom Time Ranges! if you don't feel like reading the entire release notes).

~If you prefer the old behavior, downgrade to version 3.3.4 and you're good to go.~ Don't, unless you're running WordPress 3.6 (the last version compatible with WPP 3.3.4) or older which isn't a great idea due to several security holes these older WordPress versions have.

[m417z]

If you prefer the old behavior, downgrade to version 3.3.4 and you're good to go.

I don't like that suggestion. I assume that stability and security update are being added along the way too, and I prefer not to miss those.

Anyway, I don't think that it's a good idea to log every visit by default, and without a way to opt-out. Hopefully, my solution of adding a UNIQUE key will work well for me.

[cabrerahector]

Yeah, I edited my comment above regarding that.

Anyways, since you already found a solution for your use case I'm closing this. If you have any further questions don't hesitate to ask.