Plugin Author
Ajay
(@ajay)
Do you have the maintenance ON? This cleans out any daily table hits older than 6 months.
That is the tracker so it’s clearly hitting the two tables and I suspect the daily is causing the issue.
Thread Starter
hbk747
(@hbk747)
This was the setting enabled:
https://ibb.co/5WxNgm0F
Any modifications needed?
Plugin Author
Ajay
(@ajay)
I’ve been checking your site and there definitely seems to be a problem with the tracker hitting the database. One thing I’m not sure about is if the database tables are creating an issue.
Can you please backup your database completely and then click on “Recreate primary key”. It would be helpful to also check out what the size of your tables are wp_top_ten* in the database.
Lastly, I’d suggest optimising the tables as well.
Thread Starter
hbk747
(@hbk747)
Okay, also just so you know I have had the fast tracker disabled because there were too many hits.
Before recreating the key, this is the situation of the tables:
https://ibb.co/pBpptmRx
I have backed up my database and I am recreating the key now
Edit: I have recreated the key.
Also how do I optimize the tables? Using wp-optimize?
-
This reply was modified 4 months ago by
hbk747.
Plugin Author
Ajay
(@ajay)
I think the issue is the 23m + entries in the table.
Are you able to run this SQL Query in phpmyadmin or equivalent.
DELETE FROM wp_top_ten_daily
WHERE dp_date <= DATE_SUB(NOW(), INTERVAL 6 MONTH);
I want to delete entries that are older than 6 months. If you have a very high-traffic site, you might need a shorter period of content storage.
Plugin Author
Ajay
(@ajay)
You can put this in your wp-config.php
define( 'TOP_TEN_STORE_DATA', 90 );
This would limit data to 90 days instead of the default of 180 days.
Thread Starter
hbk747
(@hbk747)
I ran the command and it deleted the entries.
I have also edited my wp-config.
Also I was wondering, could these really old entries be in the database because one of my top 10 widget’s is set to “Overall” in the period section.
I hope this has fixed the issue.
Edit: Is it safe to turn on the Fast tracker now?
-
This reply was modified 4 months ago by hbk747.
Plugin Author
Ajay
(@ajay)
Yes please turn it on and check if this has been working fine. I have a feeling the old entries was the issue.
What is the current count that you see in the daily table from the 23 million count.
Thread Starter
hbk747
(@hbk747)
Hello,
I will turn it on.
Also the entries are still a lot. It is showing 18M still.
Any ideas?
Plugin Author
Ajay
(@ajay)
If you don’t need that much data you can run the same command and instead of 90, use 60 or 30 days so you only retain data for this period.
Thread Starter
hbk747
(@hbk747)
You can be a better judge of that.
So my webiste recieves around 100,000 ish visits per month. There are a a few Top 10 widgets enabled. One is titled Trending which shows the most visited posts in the past 7 days. The other is titled Most Read – All Time which has the “overall” period enabled. In the Most Read – All Time, the most famous articles are shown. If I use the command to delete 30 or 60 days, what effect will it have on these widgets? Will the overall period show articles that have done well in the 30 or 60 day period or will it still show the best articles from the start of the plugin’s time?
So based on this information, should I run the command for 30 days or 60 days. If the data is inconsequential to me, I don’t mind deleting it but if it will affect the accuracy of the data then things are different.
Also if the All Time – Most Read is causing slowness issues due to the database or whatever, I can with a heavy heart change it to Most Read – Monthly. Meaning that I can change the period from overall to 30 days. Since those Most Read All Time articles might disappear because of this, I hope it wont cause an SEO issue – since all of a sudden those articles disappear from most of my pages (I have the widget active on almost my posts).
Warm regards,
Plugin Author
Ajay
(@ajay)
I need to write up an article of the tables in Top 10.
100k a month is huge so I am glad the plugin is working well on your site. My suggestion is to first run it at 60 days – see if it’s a performance issue and then bring it down to 30 days or even lower.
The daily tables are only used for the custom period (daily, weekly, monthly etc) when you select that in the widget. If you choose overall, then it uses the overall tables which only have an entry per post. So that widget can stay as-is.
The daily tables also feed the chart in the dashboard in your admin area. If you’re not overly particular about very long time data, you can delete everything older.
Thread Starter
hbk747
(@hbk747)
Okay will do. Putting the new command for 60 days in the Wp config.
Also the database entries are still sizeable, around 18M. Should I delete more or let them be? If yes to the delete option what should the time period be. You gave me the code to delete entries older than 6 months earlier.
Plugin Author
Ajay
(@ajay)
Same code as earlier but use 1 month I’d suggest given that you don’t use the data beyond that period.
DELETE FROM wp_top_ten_daily
WHERE dp_date <= DATE_SUB(NOW(), INTERVAL 1 MONTH);
Thread Starter
hbk747
(@hbk747)
Sounds good. I deleted the entries and set the save state to 60 days for now.
Lets see if this makes a difference.
I think you are correct about these issues.
Will keep you posted.
Warm regards,
