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