Auditing Your WordPress Database

I am going to give you a glimpse into my soul and tell you about the OCD I have about my WordPress database and why I audit my database on a regular basis.  Feel free to join me on my downward spiral.  I hear the cuffs on straight jackets no longer chaff

It’s Alive, it’s Alive!!

Your WordPress database is alive and growing all of the time.  As you add posts and get new comments, they are added into your database, this is great, but there are also other reasons your database can grown, and these often go unnoticed

As you add plugins, they often create new tables on your database, you may have a forum or affiliate software, this will create new tables on your database, are you aware of them?

Why Audit Your Database?

There are a number of reasons to audit and clean up your database and they are:

performance – a large database needs more resources and can degrade in performance

Backup and recovery needs – are you aware of all the tables on your system, are you backing up all of the data you need?  An audit shows new tables which need to be added to your backup schedule.

Hosting issues – your hosting provider may have an upper limit on database size or you may pay a premium for space, reducing the size may solve these problems.

Security – Have you been hacked, are there hundreds of credit card numbers in your database from a Paypal phising attack?  An audit of your database can show discrepancies like this.

Backup

Before we start messing around with WordPress database tables, we should always do a backup, what should be do children? (I’m cupping my hand to my ear waiting for your reply).

Default Tables

The default tables for a WordPress install are shown below, don’t touch these or it will end in tears.  Please note your prefix may be different.

wp_comments
wp_links
wp_options
wp_postmeta
wp_posts
wp_term_relationships
wp_term_taxonomy
wp_terms
wp_usermeta
wp_users

Doing Your Audit

I cannot give you the exact specifics of an audit for your site,every blog is unique with different plugins and addons, but here is what I do.  I do all of my DB admin using phpmyadmin, check with your host to see what tools are available.

1. Check the default tables make sure nothing is untoward there.

2. Look for unidentified tables or unusual tables.  I know it’s a bit woolly, but I have just done a quick check and I can see tables for an invoicing plugin I tested but rejected, they are on the list.  I also saw some weird tables with a qu prefix I did not recognise, a look at the data shows it is my affiliate system, I need that but the audit has helped me to know what is valid and what is not.  I  put these tables names on my list.

3. I check last updated times on tables, if they have not been updated recently, they can go on the list as potentials to get rid of.

During you audit it may be a good time to run an optimize table routine,  this tidys your database and indexes to improve performance.

What to Remove

So I have built a list of tables to get rid of and suspect ones, the suspect ones I leave alone till my next audit if they have not been updated they are dropped, the ones I definitely know I don’t need are killed on the spot.

In database parlance you drop a table, look for that button on your database admin tool.

For The Seriously Messed Up

If you are getting into my mania, why not document your database.  A list of tables and relationships just like the corprate database administrators do.

I am only stage two barking so I don’t bother, but you may have the urge, there are even tools to do it LINK REMOVED DUE TO MALWARE ON SITE (no affilaite link, I am not that desperate).

As I Fret Myself into an Early Grave

Go on have a look at your database and see how untify it has become, a quick audit of your tables can improve your database performance.

It’s friday we always have waffles on Fridays, where are my waffles, WHERE ARE MY WAFFLES!

Oh yeah and I audit my plugins too.