On today’s episode we are going to talk about how we can make sure our database is nice and small, lean and mean, right here on Your Website Engineer podcast 485.

Hello, and welcome to another episode of Your Website Engineer podcast. My name is Dustin Hartzler. I hope that you are well, and that you are enjoying this quarantine season, if you will. It is April 1st, as this is being released, and just that last few weeks have been really crazy. And it seems like it’s been year, or months even since we’ve had Word camps and, you know, and other WordPress related events. And I’ve had travel meet ups cancelled, and all kinds of things, but I’m glad that we’re able to spend time with out families, being home, the weather is starting to get nice outside, so that’s just a little update here. I’m working on all kinds of house projects, just knocking little things off the to-do list all the time.

And I – I personally – I mean, my life hasn’t changed a whole lot. I’ve just been at home, and continue to stay at home, and the only difference is, I’ve got more people in the house now all the time, but other than that things are good and let’s go ahead and dive right into the show. And one other thing that came out of this whole virus thing with people being home is people were able to focus on WordPress, and get WordPress 5.4 out the door.

WordPress 5.4 code name, or the name of it is Adderley. Is named of Nat Adderley of the – it’s the latest and greatest version of WordPress. There is a lot more that has come with WordPress 5.4. It adds more to the Block Editor. There’s more ways to make posts and pages come alive with your best images. There’s more ways to bring visitors in and keep them engaged. There’s more vision – or there’s more ways to keep your vision real and put blocks in the perfect place. There’s all kinds of things in this latest version.

And some website or webhosts have already updated to 5.4, other ones you’re going to have to go in and manually click the button, but I do recommend doing a full backup – this is a great time. We’re in the spring season, you know, two or three times a year do a full backup, and then push that off site so it’s not longer on your server, and then update to the latest version of WordPress. That is WordPress 5.4. I haven’t updated all my sites yet, but it is something that’s on my list to do, and I’m excited about that.

Then next thing on my list today to share is something that’s not really WordPress related, but it’s kind of neat, that the Font Awesome team has released a new COVID-19 awareness icons. In case you’re not aware of what Font Awesome is, is it’s those icons that you see that people put in menus. They put them in different places on their website, and there’s just a whole standard array of icons that you can use and then you can customize them with CSS. You can change the color. You can hoover over them. Have a different color, all those good things. And now that have a new block of code, or block items that are a part of the COVID-19 awareness. And so, it talks – there’s toilet paper in there and hands wash and a – a faucet, a medical clinic, an ambulance, heart and lungs, and, you know, just some things that are very much, kind of, of what we’re seeing and what we’re learning in this pandemic.

And so, if you’re interested you know, finding out more about that there’s a link in the show notes to the WP tavern article, and it talks all about Font Awesome and their – these new icon sets.

I’ve got two webinars that I want to share with you. Up next in the – in the – in the notes here for – for the episode, and the first one is happening tomorrow, April 2nd, and it is a 16-minute free webinar put on by the folks by WordPress.com. It is at 2 p.m. Eastern, 11 a.m. Pacific. There is a Zoom link to – to get in there if you are interested. But it’s basically a – it’s a webinar that will be replayed later on the YouTube channel, but it’s advice for how to make a great website for your small business.

I’ve seen just tons and tons and tons. We so busy at work because so many people are asking about, you know, how do I change my regular store into an E-commerce store? You know, they’re trying to turn – trying to install WooCommerce and then add shipping and, you know, have people pick up delivery or take-out or all these different types of things, and so we’re just really – really business. And this is a great webinar to get a hands-on look at how to really set up a business – or set us a website for a small business. So that is the first webinar, and again there’s a reply – there will be a replay for that.

And then the second webinar is a free on-line event and it is – all about the Block Editor. It’s called WPBlockTalk. And it’s just talking about building into the Block Editor. What it’s like to develop the Block Editor. What features. So, this is more of a developer level webinar and there’s a link in the show notes for this as well, where you can sign up. And this one will be on WoodPress.TV, that you can re-watch, or watch for the first time whenever you get a chance. And so that is something really – really cool. It’s really neat to see that WordPress.com is taking some initiative and doing these free sessions just to help the community out, and to really kind of embrace this – this weird time in the world where we’re all starting to work online, or there’s a lot more activity going online. So those are the announcements that I want to share with you today.

I have a plugin to share with you, and this one is called, Hide Admin Notices. And I don’t know if you’ve done this before, logged into somebody else’s site, and then had to scroll before you could actually do anything because there’s so many admin notices. The – the first thing, instead of installing this plugin, is to actually address those notifications, but sometimes that’s not really a – or a practical thing, maybe that’s not something that you can do, or maybe you want to hide them from your users, or hide them from the people that are using the website, but not from the admins. And this plugin is called. Hide Admin Notices, and it basically will take all of those notices that are jumbled up at the top and put them in a tab up next to the help and the screen options icon.

And then if there’s actually one that’s active, you can click the – they’ll have a little red notification icon there, and you can click on that and you can expand and then you can see what things need taking place.

I’m not saying to use this plugin to just hide all of the – the notifications and not do what the plugins are asking, but it’s one of those things that it’s really nice to have just you know, if you’re on a development site, or, you know, you just want to get rid of those notifications without having to deal them.

So, first deal with them properly, and then second of all, this is a good way to hide those notices. And you can search for this in the WordPress repository, it’s called Hide Admin Notices, or as always you can find the link in the show notes for episode No. 485.

All right, today I want to take about databases, and trying to make sure that our databases are really just streamlined and optimized. And I came across this because I was trying to help my wife get one of her websites up and running. There was an issue that I was running into. And then there was another friend that I was helping with, and his website that he was trying to restore was a very large file, and it’s just like why are these database files so extremely large? And so, I went down a rabbit hole to try to figure out what’s going on and give you some helpful advice to try to get rid of some of this information that’s on there.

First, I want to talk about a plugin called Advance Database Cleaner, and this is a – a plugin that will allow you to clean up a – you know, your database with basically a click of a button. It will allow you to delete old revisions of posts and pages, delete old audio drafts, delete trash post, delete comments or pending comments or spam comments or pingbacks or trackbacks, delete, you know, all kinds of orphaned meta data that’s no longer needed. You can have a database scheduled to do this automatically. You can look for, you know – you know, it can optimize database tables. It can do all this kind of stuff. So, this is a good – I guess, a good way around. You’re manually going in and fixing things. This is going to get rid of a lot of extra junk.

But in the case of my wife’s site, when I was looking in hers. It was giving us a – it was giving me a really hard time. It was, like, why is the WP options table – that was something like 38 megabytes. That is humongous. You know, a full WordPress database should be, you know, between five and ten megabytes; the entire thing. And her WP options table was humongous. And I was, like, what is going on? Why can I not figure out where this option is? And I started to go through, and it was kind of tedious and the – the way that I tackled this, okay, I knew it was in the WP options table, so I logged into the host account, and then went into PHP My Admin, and then I started looking around in the – in that WP options table.

And I knew that for that to be so big there was either going to have to be thousands upon thousands upon thousands upon thousands upon thousands of records in that database, or there’s going to a couple records that are just insanely huge, and just – is just really weird for some reason. You know, a table might have – usually a table – or an option inside the WP options table, as you know, one, two, three, four lines long. You know, the ones that are –might have some settings and stuff saved in them. Could have some HT – well, it might be a dozen lines long or something along those lines.

And so what I ended up doing was, I went into the – the PHP My Admin, like I said, I got into the WP_options folder, and I started looking in, and I noticed there was – I don't know, maybe 25 pages of options with 25 options per – or 25 rows per page, and I’m, like, okay, that’s not super – super long. And so, I started checking through them, you know, just kind of going from clicking the arrow from 1 to page 2 to page 3, and just looking and seeing if there’s anything outstanding that just looked weird. And I couldn’t find anything. And I was, like, I don’t know what’s – what’s – what could be causing this.

And then I discovered this – this cool little section inside of – there’s an option right above all the tables, there’s an options area and there’s one that’s – by default you’re seeing the partial text, but I went to the full text menu and then I clicked go and then basically instead of showing everything in one line it shows everything in multiple lines. So now all of sudden a page with 25 items, instead of being what I can see in one view, now I’ve got to scroll. And for most things, like I said, they were pretty easy, active plugins that – that option was maybe eight lines because it shows all the plugins that were active. There’s another one that’s got moderation keys, or blog cara set, or there’s different things that they’re not super – super long and so you could go from page to page to page. And what happened was I got to page 24, and all of a sudden it timed out. It wouldn’t load, and I was like, what is going on? Okay, so it must be on page 25.

So, since it wouldn’t load, I went back to page 24, and I went back to the options and changed it back to the partial text, so I knew it was on page 25. And it couldn’t load because there was too much data on there. And then I started – then that narrowed it down, so now I had 20 – I had 25 pages, 28 pages, however many there were. There was a ton page pages of options and know I knew it was on page 25. So, okay, so now I’ve limited – got it all the way down to there. And then I could start clicking on each option, and the first option that I clicked on had – I opened it up and it just scrolled and scrolled and scrolled and scrolled and scrolled and scrolled. And I had five of those options. And I ended up – okay, I figured that’s what my problem was.

I couldn’t really see – it looked like it was something from an old backup of VaultPress with that website, and so I’m, like, yeah, it’s probably not important. And I couldn’t actually get my backup to work because these files were so big, and so, what I ended up doing was I just opened it up.

You can – if you’re in WP options and you see the option value , you can just double click that and it opens an inline text editor, and – or it’s almost like a text area field, and then you can just highlight everything, and the I hit delete. And then I saved it, and then I did – well, I guess, when you hit delete and then you hit enter, it saves it. And I did that two, three, four more times to get all of them out.

And then I went back to the main view of the database itself. You can click on the main database and then you can look at the structure and they’ll show you a number of rows that there are in each of those tables. And that went down tremendously. It went from however many – I guess, the rows didn’t go down that much. Some databases show rows, some databases show how many megabytes it is. So, I instantly then just tried to download the – the file again and it was so much smaller, three, four megabytes for the whole website. And it was that’s where the problem was, and I was able to fix it.

Other things that you may want to look at when looking at the database and figuring out why is a database so big, there’s a few plugins that really you want to look at because they can create a lot – a lot of data.

The first one that I have on my site is Pretty Links, so Pretty Links is a way that you can create those long – or you can create a short URL for something long. Maybe if I wanted to go – point you directly to hoover, like, as an affiliate link, go to Your Website Engineer.com/hoover, and that will automatically redirect you from my site to hoover with my affiliate code built in, so, I can do that. But with the Pretty Links structure every time somebody clicks on those links that is all recorded in a log, and each log creates its own – it time it’s clicked on will create an entry in the database here.

So, if I’m looking here – I went into the – and I’ve – I’ve reset these before because there’s no sense of having them – keeping all of this information on there all the time. But now I have – I’m looking at rows of 25, and I have 300 rows of 25 pieces of data in the database saved for Pretty Links, just to let me know how many times people have clicked on different links. So, that information is not very useful.

There’s other plugins that are out there that will log everything that people so on your website. Maybe you want see if people are logging in, or they’re changing their passwords, or if they’re doing – you’re updating blog posts, or whatever they might be doing. Every little thing is logged in the database. And the more time that it’s logged, the bigger the database is going to be and bigger that file is going to get.

And so that is something to look at when you’re trying to move databases around. This is really – obviously it’s going to help your – your impact with your load time if your database is smaller, but the really thing is if you’re trying to backup or you’re – if you want to keep the last seven backups, if your site is 3 megabytes versus 300 megabytes, you’re going to save a lot of hard drive space. If you’re trying to upload a site from one place to another, or move it to a host, like, if it’s 300 megs, like, some platforms will only let you upload 200 megs into their PHP My Admin, so it’s like, okay, well, let’s see if that’s – you know, we want to keep them small and optimized.

The other thing – so – so we’ve talked about there may be a few options that just has tons and tons of data that’s going to make it way too big. We’ve talked about looking for those plugins that have a lot of records, and if you’re not comfortable – like the Pretty Links settings inside of the WordPress dashboard, I can go in there and I can say, you know, get rid of all of the information from anything older than 30 days. And that’s going to make that database much – much smaller. Do I need to keep the last nine years’ worth of data when people are clicking on links? No, not really. Unless I’m really wanting to focus on who clicked on this link, or how many people are actually using this link? Is it helpful? If you’re trying to glean some analytics off of who’s using what links, like that may be helpful to keep 90 days or 180 days’ worth of information, but it’s not necessary to keep all of that information.

So, you want to look at those. You want to look at anything that’s tracking what users are doing, or anything even Google analytics, redirections or things like that, that might be triggering – that might have actions saved there in the database. The iThemes Security plugin with the logs there, that’s probably generating quite a bit of data if there’s lockouts. I mean, look at that section. Actually, there’s nothing in the I Security logs right now for me, and so that’s – that one is one that I’ve – I’ve cleaned out recently before.

And then the last thing that we want to look at – and I’m looking here at the database tables for Your Website Engineer.com and it looks like – I’ve got some plugins here that I’m not – not really interested in keeping anymore. I don’t have those plugins anymore. Like, one I see that I have a table in here for Livefyre. Livefyre was a plugin from many moons ago. It was – let’s see, I’ve got 25 rows, I’ve got 40 pages of 25 rows of just data from customer – or people that used to use Livefyre, which was the – back in the day that was one of the platinum level commenting systems. It’s not even a thing anymore. And so, I can see, oh, I can probably get rid of all that information.

I see that I have all of the – I’ve got – let’s see, one, two, three, four, five, six, seven, eight, nine, ten tables here from Gravity Forms. I don’t even use Gravity Forms anymore it’s – I’ve switched over to Ninja Forms and so there’s a bunch of stuff that’s there that can be removed.

And, like I said, the Pretty Links are there. Other than that, most things I’m still using that are there. But other than that, most of these things could be completely just removed, get rid of the tables itself because you’re not – there’s nothing – there’s no reason to keep them around and no reason to have a great big file when it comes to the database.

I did look at my database right before I started recording and it is sitting at 31 megabytes, so I have a little bit of work to do. I’d like to get that down to that ten-megabyte limit or so, just so it’s a little bit – a smaller of site, and why not? So, I can probably easily just go through by getting rid of some of these things that I’m not using and clearing out the Pretty Links, I could probably get this down to ten-megabytes pretty easy.

But that’s what I want to talk about. It’s probably not essential to do, like, to – you know, just stop everything that you’re doing and clean up your database, but it’s one of those things that there’s – it’s – it’s a good practice to have. It’s a good practice to even install the plugin – like I mentioned earlier, the Advance Database Cleaner. Go through and just clean up things that you can. Make that database a little bit smaller. Make it just so – in case you have to move to another platform or maybe you want to start developing locally and you want to bring your website down from your – from your live sites. Maybe bring it into a desktop server or local by Flywheel or any of those different products. This would be a good thing to do and just optimize things.

Always make a backup before you make some drastic changes like this, like, deleting data and – and changing database files or whatnot, but that’s what I wanted to recommend in this week’s show of Your Website Engineer podcast.

Hope you are well. Hope you are staying at home. And hoping to contain the spread of this virus and hopefully one day soon we’ll be able to get back to Word camps and get able to see people face-to-face. I’ve seen so many people doing cool things with Zoom, and just cool – just cool ways to stay connected and just having game nights over Zoom, and our kids are starting to do class via Zoom and whatnot. So, it’s just an interesting time, and exciting to get past this. Get through the summer months and just spend some time outside.

So that’s what I want to share with you this week. Take care. And we’ll talk again soon. Bye-bye.