Reducing WPP Database Table size

Hi Indy,

Yes, there is: Data Logging.

Additionally, but 100% optional, using that in conjunction with the Pageviews Cache will guarantee that WPP’s tables remain as compact as possible.

Hi Hector,

Thanks for your swift reply.

So with Data Logging, it does not impact the overall views for the post? Is this because it is stored in another table?

So, the table where the time-based views are being deleted is not the total count?

Which tables are affected? Can I still get views in the reports for say the last 7 days?

Also, if I want to reduce the size of the current database with respect to the above, is there a query I could run?

Please advise.

Thanks
Indy

HI Hector,

Also, how does Data Logging impact widgets which use 7 days, 5 days views data and posts published within that period?

e.g.

https://prntscr.com/12d52sm

Please advise.

Thanks
Indy

That’s all clearly explained in the link I shared earlier ?? Here are the related bits:

Note that this will only delete views data related to Time Ranges (last 24 hours, last 7 days, and so on.)

and:

The total views count of your posts and pages won’t be affected by this in any way.

What gets deleted depends entirely on how you configure the Data Logging feature.

Is this because it is stored in another table?

Correct.

Also, if I want to reduce the size of the current database with respect to the above, is there a query I could run?

The very last paragraph of the FAQ I shared above has a suggestion regarding this, but if you don’t want to use a plugin then you can also optimize any database table manually via the OPTIMIZE table statement. The suggested plugin “just” automates this for you but if you don’t mind executing this query manually then that works too.

Also, how does Data Logging impact widgets which use 7 days, 5 days views data and posts published within that period?

If you set the plugin to keep only 5 days of data, the widget can display only up to 5 days of data no matter what time range you select (except for “All-time” of course.) Same thing applies to the Stats dashboard.

• This reply was modified 3 years, 7 months ago by Hector Cabrera. Reason: Reworded for clarity

Hi Hector,

After running the OPTIMIZE command and switching on the caching, the database size went up by 1GB! Not go down as I thought it would?

https://prntscr.com/12e4etd

It was 4GB before.

Any ideas why this has happened? I am worst off now than before. How can 1GB be used up so quickly for this?

These are my settings on the WPP plugin

https://prntscr.com/12e4h9t

Please advise.
Indy

• This reply was modified 3 years, 6 months ago by indyparker.

No idea to be honest. The OPTIMIZE query is supossed to “reorganize the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.” Your database growing in size after using it is unexpected to say the least. Might want to reach out to someone more knowledgeable in database stuff than me to find out more.

Hi Hector,

After making the changes to the plugin settings. It looks like WPP is showing incorrect views for today:

https://prntscr.com/12fbhol

This is very confusing. Because on GA the views are much higher (before it was the other way round):

https://prntscr.com/12fbit0

Can you please help me understand what is going on? Do I revert the changes for WPP? Or is something else causing this?

Here are the settings

https://prntscr.com/12fbjy4

Regards,
Indy

None of the settings you’re currently using with the plugin will affect the way WPP tracks pageviews so the difference in views data is being caused by something else. Maybe reviewing these FAQs can help you figure out what’s going on:

• Is WordPress Popular Posts compatible with caching plugins?
• Is WordPress Popular Posts compatible with security / firewall plugins?
• Is WordPress Popular Posts compatible with plugins that minify / bundle JavaScript code?
• I need help with your plugin! What should I do? (read second paragraph).
• Popular Posts View Count Different Than Jetpack? (Also applies to Google Analytics and any other statistics tool out there).