Reduce size of wp_popularpostsummary ?

Hi Tooni!

Since version 3.3.0, you can limit the amount of data that WPP stores on your db. Go to Settings > WordPress Popular Posts > Tools, and under Data you’ll find the Log limit option.

Basically, this feature limits the amount of data stored in the summary table. By default, records older than 180 days (~6 months) will be automatically deleted every day at midnight. This is configurable as well, of course.

Thank you for your excellent and fast support!

I enabled the Log Limit and set it to 400 days in order to keep some more than one year of data.
However – I’m not sure, if it worked or not, because I didn’t note the number of records before and after. But the size of table still remains the same!
Is there anything else to do? Some kind of compression or things like that?

Best regards, Tooni

Nope, you don’t need to do anything else. WPP will trim your summary table at midnight every day (must’ve been done by now) to prevent running potentially heavy queries on your database when your site is most active.

I quite believe that records are deleted at midnight – but does it reduce the tablesize? Or does is just create gaps?

When I look at the tablesize, it says:
Data – 4.5 MB
Index – 12.1 MB
Total – 16.6 MB

For me it looks like that there is still some kind of “re-organisation” neccessary?

Best regards, Tooni

I believe that the table size should shrink after removing records – but honestly speaking I’m not sure. Never paid attention to that before. Will investigate and report back once I get some spare time.

Apparently, for InnoDB tables indexes being larger than data is normal (see here and here) so I wouldn’t worry about that. In any case, the index will be updated after every insert (which WPP does quite frequently).

You didn’t say how large your summary table was before, but since it had over 3 years of data running an OPTIMIZE TABLE query may be a good idea at this point.

Thank you for thinking about my questions.
Finally also an “Optimizise Table” didn’t change anything.

Technically everything is working and looks fine, but I still have this feeling, that it cannot be normal for a popular post table taking more space than all the posts themself.

However – I will mark the question as solved.
This is a free tool and it works fine. And I’m happy to use it.
Thank you and best regards,
Tooni

Keep in mind that the summary table is basically a log: it stores the daily views data for your posts which allows the existance of the Time Range feature (Last 24 hours, Last 7 days, etc). Because it logs data every day for every viewed post / page, it’ll end up being larger than the posts table.