How to clean up the WordPress database

WordCamp Seattle 2018

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

The Prefix

WordPress uses wp_ by default but your database may use another

 

(You can change this setting in wp-config.php before installation)

 

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" space (not)
  • Digital Ocean VPS: $5/month for 1 GB RAM / 25 GB disk space
  • Flywheel: $15/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" time is part of "time to first byte" (TTFB)

Wait time should be...

  • Google says: Less than 200ms
  • 100ms or less is attainable!
  • High traffic will increase wait time and TTFB

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

Caching

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

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

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

Most serious database issues are because of plugins

Facts only:

Credit: Ronald Woan

Problem plugins:

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

Vet your plugins first

  • 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!

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

Starting with SQL

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';

Let's do some code!

How to clean up the WordPress database: WordCamp Seattle 2018

By davidegr

Made with Slides.com

How to clean up the WordPress database: WordCamp Seattle 2018

Database talk for WordCamp Seattle 2018 by David Greenwald.

  • 627

More from davidegr

  • How to clean up the WordPress database: WordCamp Portland 2018
    davidegr
    2318
  • How to clean up the WordPress database
    davidegr
    579