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 = Fullstack Developer. Book Author. Teacher. Human Being.
WP Themes In Depth: Build and sell awesome WordPress themes.

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

  1. I am going to try this as I have one good blog in which wp-shortsat takes more then 60 secs to laod..

  2. Hi there’s any plugin for replace this wp-shortstat?

  3. $30 for the statistic only. i will think it twice or more.

  4. Yes, there are several, last time I checked. Also recommended is Shaun Inman’s awesome Mint statistics, which I use for all of my key sites.

  5. Then definitely should be able to find a plugin that will provide some good statistics. FireStats, I’ve heard, is one of the more advanced stats plugins.

  6. DarkKnightH20 2010/08/08 6:19 pm

    Thanks a bunch for the tip. Previously, I use to just clear out 99% of the database and lost some of the useful information, but this way is better.

    I went from 1,236,294 rows to 50,000 rows.

  7. Thank you so much for this – I was tasked with upgrading a friend’s wordpress 2.5 blog to 3.0.4 (and mysql 4.0 db to 5.0) and had noticed this ss table at 50MB… so I cleared it out as they were starting anew anyway.

    Perfect and great write-up! I love blog posts that make a quick answer for copy/pasters but also the background info for those who want to learn.

  8. Ken Maddox 2011/01/23 11:15 am

    THANK YOU!
    THANK YOU!
    THANK YOU!

    My database limit is 300, shortstats had run it up to 467. It finally got so full it shut my site down and this helped me tremendously in getting it back up.

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 »
Banhammer: Protect your WordPress site against threats.
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.