WordPress Tip: Reduce the Size of the WP-ShortStat Database Table
In this article, I present a simple method for dramatically decreasing the size of your WordPress database by partially emptying old data from the WP-ShortStat table via the following SQL command:
DELETE FROM `wp_ss_stats` ORDER BY `id` ASC LIMIT nThat is the point of this entire article, which dips into just about everything one might need to know before employing such strategy. If you are familiar with SQL and understand the purpose and functionality of this command, feel free to grab, gulp and go. Otherwise, read on for the full story..
A little context, please..
Many WordPress users enjoy the convenient statistics provided by one of the excellent ShortStat plugins. WP-ShortStat keeps track of many essential types of data: recent referrers, search queries, referring domains, keywords, locations, browsers, and many more. Over time, the copious amount of statistical data collected by WP-ShortStat increasingly inflates the size of your WordPress database.
For example, before installing WP-ShortStat, my WP database was around 8 megabytes in size. After installing ShortStat and using it for several months, the size of my database ballooned to well over 30 megabytes! WP-ShortStat uses these data sets to calculate cumulative totals, such as total hits, daily visits, referral counts, and various others.
Fortunately, I supplement the functionality of ShortStat with Mint, which provides all the statistical data I will ever need — without bloating the size of my WordPress database (Note: the Mint database is maintained independently of the WP database). Thus, my statistical strategy involves using Mint to record permanent, long-term data, while simultaneously using WP-ShortStat to track daily hits, referrals, and visitors1. At the end of the day, the ShortStat data is completely expendable and fails to justify its behemoth table size.
Nevertheless, even without running a secondary statistical package to supplement WP-ShortStat, many users would gladly trade their running tally of cumulative statistical data for a more lightweight and agile WP database. If this sounds like you, or if you really don’t care about all this long-winded nonsense, here is a simple way to reduce the overall size of your WordPress database by cleaning up your WP-ShortStat table.
Getting on with it, then..
Thanks to an informative discussion with Mark from c77studios.com, a single SQL command was forged to quickly and efficiently remove old data from the WP-ShortStat database table:
DELETE FROM `wp_ss_stats` ORDER BY `id` ASC LIMIT nAs discussed in the original conversation, this command is a generalized SQL query that is independent of table data such as ID. Execution of this command will effectively delete the oldest n number of records from the WP-ShortStat table, wp_ss_stats. Further, the generalized syntax of this command enables us to automate the procedure at specified intervals via cron job, PHP, etc.
I use this snippet of SQL to shrink my WordPress database every month or so (kind of like shaving). Rather than try to explain the overall effect of this command, here are a few screenshots showing my WP-ShortStat panel and its associated wp_ss_stats table at various points throughout the process1:
I began with this hellishly sized table, which was over 180,000 rows thick and over 45,000 Kilobytes in size:
![[ Screenshot: showing the relatively immense size of the WP-ShortStat table ]](https://perishablepress.com/wp/wp-content/images/2008/wpstats-flush/stats-table_01.gif "Indeed, after only a month or so, our ss_stats table is over 180,000 rows thick and over 45,000 Kilobytes in size.")
Two chop the table size in half, I divided the total number of records by two and entered the following query:
![[ Screenshot: showing the SQL query discussed in this article with a value of 97257 ]](https://perishablepress.com/wp/wp-content/images/2008/wpstats-flush/sql-command.gif "To demonstrate the technique, I chose to chop the table size in half by dividing the cardinality value by 2.")
After executing the query, the oldest 97,257 records were removed from the table, effectively reducing its size by roughly half:
![[ Screenshot: showing the halving effect of the previously discussed SQL query ]](https://perishablepress.com/wp/wp-content/images/2008/wpstats-flush/stats-table_02.gif "Here we see the expected result of deleting the oldest half of the table records.")
Within the WordPress Admin area, here is a portion of the WP-ShortStat panel before deleting any data:
![[ Screenshot: showing a portion of the WP-ShortStat panel before data removal ]](https://perishablepress.com/wp/wp-content/images/2008/wpstats-flush/shortstat-panel_01.gif "This is how the WP-ShortStat panel appeared before data deletion.")
After executing the query, the cumulative totals have been decreased by roughly half (as expected), while the most recent data remains intact:
![[ Screenshot: showing a portion of the WP-ShortStat panel after data removal ]](https://perishablepress.com/wp/wp-content/images/2008/wpstats-flush/shortstat-panel_02.gif "This is how the WP-ShortStat panel appears after data deletion.")
Conclusion
As you can see, this simple query serves as an effective tool at reducing the the overall size of your WordPress database. Especially if you are fronting from a limited hosting plan, this trick may enable you to run one of the WP-ShortStat plugins without exceeding any limits.
Keep in mind that, depending on how many records you decide to dump (based on the value of n), the amount of data that remains for various daily totals may also be affected. If you are unsure as to which value to use for n, determine the total number of entries via the cardinality value and then try using a relatively small n value; run the query, examine the results, and then try a slightly larger number. If you remember to backup your wp_ss_stats table before beginning, it is totally safe to experiment with different values for n until you determine the optimum value. If you happen to delete too much data from your table, simply restore the backup and try again. Either way, I wouldn’t stress too much — the table will eventually be refilled with more data ;)
Footnotes
1 The information (and screenshots) presented in this article were initially prepared in October of 2007. Since producing and finally publishing this article in January of 2008, I have discontinued use of the WP-ShortStat plugin altogether, opting instead for a nice Mint/Google Analytics alternative.
20 responses to “WordPress Tip: Reduce the Size of the WP-ShortStat Database Table”
Just a tip concerning cumulative stats.. if you wanted to preserve, say, monthly totals, you could export the data into Excel or even capture a scrolling screenshot of the entire ShortStat admin panel. You could do this every month, or as frequently as you clear out the table data.
I’d really like to know what you find useful in Mint, that Google Analytics doesn’t provide.
Note : it seems that your footnotes links are broken.
I’ve also noticed that on the top of the comments list, the “x Responses” take in account the trackbacks, so it seems inaccurate as you’ve hidden them.
I don’t want to be unpleasant, but I think I wouldn’t be happy to discover that kind of little bug on my own blog and be ashamed that it’s been months visitors have seen them.
@Louis (comment #2): I love Mint because it displays my stats with elegance and sophistication. I love Analytics because it provides a wider perspective on site statistics. And, to answer your question specifically, Mint provides a pepper called “Session Tracker,” which provides real-time stats on current visitors and their navigational paths.
Also, I am confused as to which “footnote links” you found to be broken.. could you please be more specific?
@Louis (comment #3): I appreciate the fact that you not only spend the time to investigate my site, but also invest the effort to share your discoveries with me. Your feedback (criticism or otherwise) is greatly appreciated. It would be cool if you could group your comments (for each post) together, but that is just me being unpleasant ;)
As for the “x Responses” display, no, they are not hidden, merely relocated for usability sake. I have found that mixing trackbacks and pingbacks with regular comments tends to detract from the overall conversational flow. I apologize if you feel cheated or somehow deceived — I assure you that I am only trying to accommodate my visitors :)
Oh! I don’t understand how I missed the trackbacks after the comment form. I’m very sorry.
However, maybe “x Responses” is a little confusing as the trackbacks have a title (“Trackbacks / Pingbacks”), but the comments follow directly after the “x Responses”.
But that’s a little far-fetched, your blog is fine :)
No, seriously, I appreciate your concerns and criticisms.. this site is still far from perfect, and thus any feedback I receive concerning such aspects serves to improve aesthetics/usability/etc. during the next site redesign.
Thanks for the tips- I had no idea WP-Shortstats was taking up so much space in my database.
I just upgraded one of my sites to the latest WP 2.5 and the latest WP-Shortstats and so far so good.
I checked out Mint, but I was disappointed to see that you had to get a license for each site. I think I’ll stick with Google Analytics for a while longer.
My pleasure, Tracy — glad to hear that WP 2.5 and WP Shortstats are working for you :) Thanks for the feedback!
I’ve deactivated this plug in but need to get rid of the huge wp_ss_stats file!!
Can I just delete it from the myPhpAdmin?
My host is about to shut my blog down – help!
Yes you may delete the
wp_ss_statstable from the database, however I would recommend making a backup copy of both the individual table and the entire WordPress database, just in case..I never really liked the WP-ShortStat plugin I always preferred not to add undue stress on the WP database. Google Analytics has always been sufficient for me. Although I have read a bit about Mint but just have not had time to test it out. It looks to be a very interesting stats package.
Technically, using WP-Shortstat doesn’t “add undue stress on the WP database.” The WordPress database is a static, well-organized aggregate of data — there is nothing to stress. The database does nothing in and of itself, but rather is used as a resource from which dynamically generated content is created by server software. The server itself, however, may experience an increase in required resources when using WP-Shortstat, but nothing more than, say, Mint, which is certainly a resource-intensive application. Even so, I traded WP-Shortstat for Mint and Google Analytics some time ago.