Spring Sale! Save 30% on all books w/ code: PLANET24
Web Dev + WordPress + Security

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 n

That 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..

Working with the WordPress database? Check out my latest book, Wizard’s SQL Recipes for WordPress 🪄 Features over 300 recipes and an entire chapter on optimizing the WP database! Check out the PDF Demo and get the book »

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 n

As 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 ]

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 ]

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 ]

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 ]

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 ]

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.

About the Author
Jeff Starr = Creative thinker. Passionate about free and open Web.
SAC Pro: Unlimited chats.

20 responses to “WordPress Tip: Reduce the Size of the WP-ShortStat Database Table”

  1. August Klotz 2008/01/01 9:38 pm

    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.

  2. 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.

  3. 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.

  4. Perishable 2008/01/05 3:11 pm

    @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 :)

  5. 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 :)

  6. Perishable 2008/01/06 9:38 am

    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.

  7. Tracy Robinson 2008/05/02 12:14 pm

    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.

  8. Perishable 2008/05/04 7:42 am

    My pleasure, Tracy — glad to hear that WP 2.5 and WP Shortstats are working for you :) Thanks for the feedback!

  9. 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!

  10. Yes you may delete the wp_ss_stats table from the database, however I would recommend making a backup copy of both the individual table and the entire WordPress database, just in case..

  11. 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.

  12. Jeff Starr 2008/09/15 8:30 am

    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.

Comments are closed for this post. Something to add? Let me know.
Welcome
Perishable Press is operated by Jeff Starr, a professional web developer and book author with two decades of experience. Here you will find posts about web development, WordPress, security, and more »
SAC Pro: Unlimited chats.
Thoughts
I live right next door to the absolute loudest car in town. And the owner loves to drive it.
8G Firewall now out of beta testing, ready for use on production sites.
It's all about that ad revenue baby.
Note to self: encrypting 500 GB of data on my iMac takes around 8 hours.
Getting back into things after a bit of a break. Currently 7° F outside. Chillz.
2024 is going to make 2020 look like a vacation. Prepare accordingly.
First snow of the year :)
Newsletter
Get news, updates, deals & tips via email.
Email kept private. Easy unsubscribe anytime.