Database Tip: Clean Up User-Agent Data

I just finished up my latest book, Wizard’s SQL Recipes for WordPress. And it’s packed with over 300 time-saving code snippets for managing and optimizing your WordPress database. For example, one of the recipes from the book shows how to delete unwanted user-agent data from the WP comments table. This is an easy optimization step that can help to free up some precious disk space.

Show all user-agent data

Did you know that WordPress collects the visitor’s user-agent data for every comment? In the database, user-agent data is stored in the comments table in the comment_agent column. But you would never know it because WordPress does not display the collected user-agent data anywhere in the Admin Area. No worries for us though. A simple SQL query will summon the information:

SELECT comment_ID, comment_author, comment_agent 
FROM wp_comments;

If comments have ever been enabled on your site, that simple query may return some surprising results. Or if you’re using an application like phpMyAdmin to manage your database, you can check out all of the user-agent data by visiting the wp_comments table and examining the comment_agent column. To give you a better idea, here is an example of a typical user agent:

Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36
(KHTML, like Gecko) Chrome/94.0.4606.81 Safari/537.36

Most comments should have some sort of user agent recorded, although some may not. It depends entirely on the client that the visitor was using when leaving the comment. Incidentally, any collected user-agent data can be used to identify which browsers/clients people (and bots) are using to visit your site.

In general, user agents can vary greatly. Some user agents consist of a small number of characters, while many others (like the previous example) consist of many characters. Either way, depending on the number of comments, user-agent data can consume a significant amount of space.

Delete all user-agent data

Before going further, if your site uses a plugin that makes use of user agents (remember, WordPress itself does not use user agents for anything), then stop right here. You don’t want to delete any user-agent data. But for everyone else — like probably around 99% of WordPress sites — all of that user-agent information is pretty much useless. So it gives us an excellent opportunity to clean up the database by deleting all user-agent information from the comments table. Here is the magic SQL recipe to do it:

UPDATE wp_comments SET comment_agent = '';

Here we replace the user-agent data with a blank/empty value for all comments in the WordPress database. If you would rather replace the data with n/a or any other string, replace '' with 'n/a' or whatever makes sense for your site.

Depending on the number of comments on your site, that simple query can reduce the size of the database considerably. And that’s a good thing.