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!
