 Okay, this is how to clean up the, this is how to clean up the WordPress database. I'm David Greenwald. I am a freelance developer and performance engineer here in sunny Portland, Oregon. You can check me out on DavidGreenwald.com and also on GitHub. And actually if you have some Wi-Fi, you might want to check that out. I have a cheat sheet of code over there that you can take home and run on your databases and get some good information. So there's that link. This is a 15-minute talk, so we're not going to teach you SQL today. The goal is just to give you a good foundation to look at these issues and diagnose them, understand the problems that we face with WordPress databases. So this is Passion of the Weiss. This is a music blog down at LA run by my friend Jeff. You may have heard of it. It's a pretty well-known site. He's been doing it for years. It gets a good amount of traffic. I think about a hundred thousand visitors a month, which is solid traffic, very respectable, not enough to break the internet. Jeff should not have any problems with his website if he gets 3,000 people on it for days. And yet Jeff's website was crashing pretty consistently. This was something that was happening to him all the time going on for years and years. So he tried a bunch of different methods to fix it. The first thing he did was move from his cheap share hosting to a faster virtual private server. Not so great. He was told he was being hacked, so he set up ClioQlare and DVOS protection and all that good stuff. This is the progress form. Finally, he ran the WP Optimize plugin and thought he was cleaning his database. And continued to have site crashing. So finally, Jeff got in touch with me and I took a look and discovered it was actually the database. So what we found out was his database was full of junk, full of trash, basically from a Facebook share counter plugin. He had deleted in like 2012 or 2013 that had been sitting there. He left all of its data in the database and just sat there. I was crashing his site for years and years. So before I... Right? I actually got these stats back. I was just checking my notes for this and it was actually 282 gigabytes, I think. So basically it left almost 300 megs of stuff sitting in his database. I went in about five minutes and cleaned it up and saved his website. It was great. So the options table... The options table, if you don't know it, it loads your settings for WordPress. So anytime someone goes to any page in your site, it's loading the options table. So it's super important to not have 300 megabytes of stuff in there that has to load every time. Jeff's VPS has one gigabyte of RAM. So if he would tweet the link to his website and five people click that at the same time, he would crash his website. So these are the kinds of problems that you can get with the database that we are trying to avoid. So what we learned from Jeff's story is that database problems are sneaky. They're hard to diagnose if you're not looking for them. They're expensive. You're probably going to upgrade your server thinking that's the actual thing. You should do that anyway, but no it's not. And it's bad for business and traffic because your website's going to be slow. It doesn't crash. It's going to be unreliable. Google hates that, so will your users. So these are important problems to know about and if you can fix them for clients, then they will be very impressed. So who thinks they sort of know what a WordPress database does? Raise your hand. That's good. That's great. Okay. So just to reveal a little, all your WordPress content lives in the database. Essentially, what happens is when someone visits your website, WordPress goes to talk to the MySQL database and says, hey, I need the content that goes on this page. The database hands it back over. WordPress says, cool. Now I can use my theme and generate an HTML document. They hand over to the user. Basically, it looks like this. That one in the back is WordPress. It's just going to keep looping over and over. But imagine you have 100,000 people on your website. This is going on all the time. So these database requests or queries take time and data. These are the two things that we really want to optimize. It should be as fast as possible and as light as possible. So what is a database? Every WordPress site has one. It stores your information. Like we said, they have tables which are ways of organizing this information. So you can think of them as a spreadsheet if you've never looked into a database before. Basically, it has rows and columns. It's a way of organizing information. So WordPress tables have rows for each item. So post one, post two, post three, and then those are broken up by the columns, which would be, you know, your post title, content, excerpt, etc. So we can sort by all of these things and the number of rows tells us how much information is in our database. So here's the WordPress, the WordPress tables that come with your site. We're not going to do a whole tour. But just to give you an example, posts are where you store your posts. Users have your users. Terms are for categories and tags. Post meta is where you would have extra information, so custom fields and things that aren't in the ordinary columns for your post table. Your site may not say WP underscore. It might be something else that can be customized just in case you run into something else on your site. Here's a couple good links to learn a little bit more about the database deliciousbrains.com and premium.wpmu.deb. Those are both really nice and you can just Google repressed database article to find some good stuff. So plugins also add their own tables. This is actually really good because it means the data is organized well. It's going to look faster. It's not all sitting in your post meta table, which is what used to happen and was bad. So WordPress is one that adds a ton of tables, Yoast SEO, WooCommerce, a lot of common plugins will add them. So that's totally fine. We just want to keep an eye out for bad actors that are really inflating your database or for plugins You've deleted that left their old tables. They're sitting there for five years and that can also be a problem. So what actually causes these database problems? Basically a large number of rows and then the data size of the row content. So let's look at that. If you had a blog post with pride and prejudice, that's 700 kilobytes of data. If you have a blog post of my tweet, it's 0.09. So if you have 5,000 blog posts and all of them are pride and prejudice versus all of them are tweets, it's going to dramatically change the size of your database and the amount of data that gets loaded and has to be searched through. So I actually think the data size, the megabyte size is a little bit more important to optimize for. My SQL is really good at looking through thousands of rows. That's what it's job is. It is less good at loading 300 megabytes of stuff into your RAM. So that's what we really want to make sure we're looking at that and optimizing. How can we tell if the database is slow? Speed test. So here's a couple of good tools, bytecheck.com, tools.pingdom, GT metrics. What we're looking for is something called time-to-first byte and that's the amount of time it takes. Somebody clicks on your website. The page actually starts loading. That's called time-to-first byte. And what we want is a little subsection of that called wait. And what wait is is the actual time on the server when WordPress and MySQL are doing that little dance that we saw. Where WordPress is asking for the information and then it can complete the page and hand it over. So this test result has 29 milliseconds. That's great. Wait, sometimes you'll see it as waiting. Time should be under 200 milliseconds. That's what Google says. That's very attainable in real world use. You should absolutely be going for that number. Anything under 500 I think is acceptable. You have to remember that the more traffic you have on your site, the more your server will work and it's going to raise that time. So you really want to make sure you are optimizing for ideal conditions. So that when you do have 100,000 people on your site that you're not getting these 10 second low times. So slow wait times are caused by not just the database but also old PHP versions. We should all be on 7, 7.1, 7.2, 7.3 just came out. Or it could be a slow server. It could be old hardware, not an SSD, all kinds of things. So in Jeff's case, he had already upgraded his server. He was on a VPS. We knew it had good hardware. We knew he was on PHP 7.0. So that allowed me to do process of elimination. And when I got the slow wait time, that told me, OK, his database is the issue. So this is something you can do when you're doing your tests to make sure you make sure you're not, you know, if you're testing on, I don't want to say godaddy.com or something else, but if you're testing on some hosting that you know is slow, that can be a problem to fix, then you can address the database as well. Caching is going to be a bandaid for your database because it skips over the database because the page is already made. So that is not going to actually help us with the database. Make sure you are optimizing it. Make sure you are doing your testing with caching off. Of course you should use caching, but it is not actually a fix for database issues. So the most important tables for cleanup, in my opinion, you never know, the options table because that looks everywhere, post meta because that's where plugins go to die, WP, post table is really easy to get cluttered and comments, and then plugin tables we want to keep an eye out for. So some good database detective tools, some plugins we're going to look at, and then SQL queries. You can put those in PHP MyAdmin. Your C panel, your server almost certainly has this installed already. Pretty easy to use. If you can go in on the command line, you can log into MySQL and just run them directly from there as well. So it works in both places. That's Sherlock Holmes. So always back up your database first. I use WP MigrateDB. It's free. It's super reliable. There's lots of ways you can migrate or you can back up your database, but this takes two seconds and really easy to use. But make sure you're backing things up before you go in and delete something accidentally. That's the opposite of what we want to do. So WP Optimize, we mentioned, it can't do everything, but there are some things it can do. In your table information, you get the records, which is what I call the rows, the number of rows you have, and then the data size. So these are the two things we're looking at. And you can use some common sense here and say, wow, I have 300 posts in my database. Why are there 30,000 rows? Or I know my WP options table should be two megabytes. Why is it 300? So you can look at what your top tables are, the biggest tables, and that can give you a place to start looking to really drill down and find out what's taking up space, what's the real problem. So what this plugin can do is clean your post revisions. Every time you hit save or save draft or publish on WordPress, it makes a copy of that post. So if you have 5,000 pride and prejudice posts, all of them are saved 30 times. That's a lot of fluff in your database that you can delete. That's great. Removing spam and trash comments, of course, is great. We used to worry about transients, but now WordPress cleans them automatically as a version of 4.4, so make sure you're on the current version of WordPress. And you're always going to want to check the optimized database tables at the end that just cleans things out. So it doesn't get the optimize as good for the posts and comments table. I like to call this spring cleaning. You should do it every few months because these things do tend to fill up, but it's not going to solve your bigger problems in the option table and elsewhere. So for advanced cleanup, we're looking at options, post meta, plugin tables. We're just going to go real quick here because we're running out of time. What you're looking for are bad active plugins currently on your site, abandoned data from old plugins like Jeff's situation, and then import data that might be hiding in your post meta from Blogger or Tumblr. I moved my site to WordPress about 10 years ago, and not only did it copy everything into posts, it copied it into post meta as well. So I was sitting there for no reason and I found this on some other client sites, and that's something really funky that you can look for as well. So some problem plugins, social media share counters, popular posts counters, anything that is counting on all of your posts, terrible for the database. A logging plugin like WordPress that tracks every person who comes to your site, tracks their IP, turn off live logging. A search program or a search plugin like Relevancy creates a database inside your database. Obviously that takes up space, so I'm going to tell you not to use these things, but if you are, be aware of what they're doing. Maybe it is a reason to upgrade your server, get some more RAM, make sure you have enough space, but those are some things to be watching out for. Let your plugins do a test install, make sure you know what it's doing, WordPress for instance, you have to check a box for it to delete stuff when you delete it. So make sure you look at those options, you know what these plugins are doing. You're going to be using them on your site for years, so take 15 minutes and understand if they are taking a dump in your database or not. So we're just going to look at one quick code example, and I'm going to refer you off to my GitHub for a bunch more. So basically if you've never looked at SQL before, what we're doing here from WP underscore options at the bottom, that's the table we're looking at. We're going to select the count of all, basically we're going to count of all the rows where the autoload column is equal to yes. So any information in your options table that's autoloading, which should be most of it. And then we're going to do that whole big function in the middle, which is giving us a megabyte value. And this is like the secret to inspecting your database. If you can get the megabyte value of what you're looking for, then you can see how much room it's taking out. And that allows you to really break down from the information we got already, from PHP My Admin or whatever. You can go deeper than what the table size is and actually find out, oh, this Facebook page is taking up this percent or taking up this much data. So you would run this SQL statement and this would tell me, here's how many rows are autoloading and here's how much data they take up. And from there I can keep drilling down. I can delete things. I can get more good information. So what your mission is, you really want to get that options table under two megabytes if you can. And then just go into your other big tables, use your common sense and figure out what plugins you used to use or you're using now that are causing problems and clean everything up. Okay, that's it. Thanks a lot.