How to clean up the WordPress database

David Greenwald

  • davidgreenwald.com
  • david@davidgreenwald.com
  • github.com/davidegreenwald

Get the code on GitHub:

goo.gl/JJ8YZE

Break the internet

Jeff's website was crashing

 

 

Here's how he tried to fix it

1. Cheap Shared Host

=> Faster VPS server

2. Hackers?!

=> Cloudflare

3. WP-Optimize

=> Clean database

Before:

252 MB

Options table

After:

2 MB

Options table

The options table loads for every page view

5 users on Jeff's website = 1GB of RAM

Database problems are:

  • Sneaky

  • Expensive

  • Bad for business and traffic

All of your WordPress content lives in the database

Database queries take...

  • Time
  • Data

What is a database?

Tables have...

  • Rows for each item - posts, comments
  • Columns for each type of information - title, content, excerpt, post status

WordPress db tables:

  • wp_posts
  • wp_postmeta
  • wp_comments
  • wp_commentmeta
  • wp_terms
  • wp_term_taxonomy
  • wp_term_relationships
  • wp_users
  • wp_usermeta
  • wp_options
  • wp_links

Database guides:

  • https://deliciousbrains.com/tour-wordpress-database/
  • https://premium.wpmudev.org/blog/devs-guide-wordpress-database/

Plugins also add tables

Wordfence:

  • wp_wfBadLeechers
  • wp_wfKnownFileList
  • etc.

Yoast SEO

  • wp_yoast_seo_links
  • wp_yoast_seo_meta

WooCommerce

What actually causes database problems?

  • Large number of rows

  • Large data size of row content

681,005 characters

700kb

~240 characters

.09kb

How can we tell our database is slow?

Speed tests!

  • bytecheck.com
  • tools.pingdom.com
  • gtmetrix.com

Bytecheck

"Wait" or "Waiting" time should be...

  • Google says: Less than 200ms
  • Definitely less than 500ms
  • High traffic will increase wait time

Slow wait times are caused by:

  • Slow server
  • Old PHP - 5.6 and under
  • Slow database
  • All three!

Caching

  • This is a Band-Aid for the database
  • Always test with caching off

The most important tables for clean-up are:

- wp_options

- wp_postmeta

- wp_posts

- wp_comments

- Plugin tables

Database detective tools

  • WordPress plugins
  • SQL queries with:
    • phpMyAdmin
    • The command line via SSH

Always back up the database first

WP-Optimize

WP-Optimize is good for spring cleaning

  • Post revisions
  • Spam comments
  • That's honestly about it
  • Always "optimize database tables" after clean-up!

Advanced clean-up:

  • Bad actor plugins currently on your site
  • Abandoned data from old plugins
  • Import data in wp_postmeta from Blogger, Tumblr, etc.
  • wp_options
  • wp_postmeta
  • plugin tables

Problem plugins:

  • Social media share counters
  • Most popular posts counters - Top 10
  • Logging - Wordfence
  • Search - Relevanssi

Vet your plugins

  • Do a test install
  • Look through the options for managing data storage
  • Does it delete its data when it is removed?
  • Do a test delete and see if data cleans itself up

Let's clean!

One SQL code example:

SELECT
  COUNT(*) AS `Autoload_Row_Count`,
  ROUND(
    SUM(
      LENGTH(
        CONCAT(option_id, option_name, option_value, autoload)
      )
    )/1048567, 2
  ) AS `Autoload_Data_in_MB`
FROM wp_options
WHERE autoload = 'yes';

Your mission:

- Get the options table under 500 rows and 2MB if you can - especially for autoloading content

- Drill down into big tables with SQL queries to find the junk plugin data and delete it

Made with Slides.com