How to clean up the WordPress database

David Greenwald

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

Follow along on GitHub!

goo.gl/JJ8YZE

Break the internet

1. Cheap Shared Host

=> Faster VPS server

2. Hackers?!

=> Cloudflare

3. WP-Optimize

=> Clean database

Database problems are:

  • Sneaky
  • Expensive
  • Bad for business and traffic

But we can fix them!

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
  • https://deliciousbrains.com/tour-wordpress-database/
  • https://premium.wpmudev.org/blog/devs-guide-wordpress-database/
  • wp_users
  • wp_usermeta
  • wp_options
  • wp_links

Plugins also add tables

Wordfence:

  • wp_wfBadLeechers
  • wp_wfKnownFileList
  • etc.

Yoast SEO

  • wp_yoast_seo_links
  • wp_yoast_seo_meta

WooCommerce

The prefix

WordPress uses wp_ by default

 

You can change this setting in wp-config.php

What actually causes database problems?

  • Large number of rows

  • Large data size of row content

681,005 characters

700kb

~240 characters

.09kb

Database Size Matters

  • Shared hosting: ~$4/month for "unlimited" (not)
  • Digital Ocean VPS: $5/month for 1 GB RAM / 25 GB disk space
  • Flywheel: $11.25/month for 5 GB disk
  • WP Engine: $35/month for 10 GB disk

How can we tell our database is slow?

Speed tests!

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

Bytecheck

Pingdom

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

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

Slow wait times are caused by:

  • Slow server
  • Slow PHP
  • Slow database
  • All three!

Caching

  • Builds your HTML web pages once and saves them
  • Makes "wait" time very fast
  • Not possible for logged in users, e-commerce, sites with 1000s of pages
  • A band-aid for the database - not a fix
  • Recommend plugins: Cache Enabler, WP Rocket

Speed test your sites without cache!

Database detective tools

  • WordPress plugins
  • phpMyAdmin
  • The command line via SSH

Server Status plugin by Little Bizzy

WP-Optimize

WP-Optimize is good for spring cleaning

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

Always back up the database first

Advanced Clean-up:

  • Bad actor plugins
  • Abandoned data from old plugins
  • 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 (Wordfence settings)?
  • Do a test delete and see if data cleans itself up

Code Time!

How to clean up the WordPress database

By davidegr

How to clean up the WordPress database

Slides from my talk at the July Portland WordPress Meetup.

  • 570