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
How to clean up the WordPress database: WordCamp Portland 2018
By davidegr
How to clean up the WordPress database: WordCamp Portland 2018
Lightning talk for WordCamp Portland 2018 by David Greenwald.
- 2,310