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

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 visitors¹. 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 process¹:

I began with this hellishly sized table, which was 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:

After executing the query, the oldest 97,257 records were removed from the table, effectively reducing its size by roughly half:

Within the WordPress Admin area, here is a portion of the WP-ShortStat panel before deleting any data:

After executing the query, the cumulative totals have been decreased by roughly half (as expected), while the most recent data remains intact:

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

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