216 – ‘D’ is for Database

216 – ‘D’ is for Database

‘A-Z of WordPress’ with Nathan Wrigley and David Waumsley

It’s the 4th in our series of chats called ‘the A-Z of WordPress’ where we attempt to cover all the major aspects of building and maintaining sites with WordPress.

Cloudways WordPress Hosting

Today, we are really going set pulses racing with the letter ‘D’ for Database!

Use these show notes to follow along with the podcast audio.

Preamble

AB Split Test plugin - the fastest way to create split tests in WordPress

The word alone sounds incredibly dull, and for years I pretended it did not exist. Even now I do anything to avoid directly interacting with it.

Are they running too many unnecessary and inefficient queries?

Do they clean themselves up after deletion?

Do I want a WordPress solution that may affect performance when there are 3rd party plugins adding to the database.

One good things to come out of GDPR is that a lot of plugin have to provide way to automatically clear up data.

Other points

  • There’s been a move recently for plugins to create their own table and not have all in options. Such as… https://yoast.com/yoast-seo-14-0/
  • If you use a tool like MainWP or ManageWP, are you even aware that the database structure might have been altered? The feedback for this usually comes as a popup in the WordPress UI.
  • What about stored encrypted passwords? How safe is the data that’s being held in the database? Are you keeping data that you don’t need which could be a potential liability if the database is compromised? See… https://stackoverflow.com/questions/31343155/how-to-decode-encrypted-wordpress-admin-password).
  • Do you change the wp prefix for security? Is there even any point to doing this?
  • How many are too many queries? Two is worse than one and three is worse than two (I could go on!), but what’s the upper limit that you should aim for?
    • “The number of queries (for example, “27Q”) will give you an idea of whether you are having MySQL problems. The number of queries should ideally be under 50. You may start to see slowdowns if the number is above 75. If it is above 150, you have an issue with your theme and/or plugins on that specific page which should be addressed.” – source here.
    • WordPress runs about 23 to start with!
  • Recently I contacted ShortPixel about adding 70+ backend queries and 50+ front end ones. Apparently, this is not an issue, as it’s restricted to certain users only when logged in. The actual speed is barely affected.
  • The cylindrical icon you get for databases is so far removed from what it really is!!!
  • Is having your database hosted elsewhere a better set up than having all-the-things in one place?

What are WordPress’ requirements ?

MySQL version 5.6 or greater OR MariaDB version 10.1 or greater.

Maria being faster, but newer and less used so certainty of support is perhaps a question. https://www.guru99.com/mariadb-vs-mysql

For no good reason at all, here’s a list of the standard WordPress tables:

  • wp_commentmeta
  • wp_comments
  • wp_links
  • wp_options
  • wp_postmeta
  • wp_posts
  • wp_terms
  • wp_term_relationships
  • wp_term_taxonomy
  • wp_usermeta
  • wp_users

Would the ideal be that you could move easily from one to the other (I think Cloudways allows one click transfers?). I have moved sites from MariaDB to MySQL with no real issues – only a couple of disconnected images.

Tools to help with database management:

Tried:

WP phpMyAdmin

Advanced Database Cleaner

MainWP Maintenance extension

WP Rocket (also does a clean up)

WP Reset

https://wordpress.org/plugins/wp-dbmanager

https://wordpress.org/plugins/wp-optimize

https://wordpress.org/plugins/wp-sweep

https://wordpress.org/plugins/better-search-replace

Never tried:

https://wordpress.org/plugins/wp-clean-up-optimizer

https://codecanyon.net/item/wp-cleaner-pro/21459036https://codecanyon.net/item/smart-cleanup-tools/3714047

When building sites:

https://wordpress.org/plugins/query-monitor (helps find duplicates and slow queries)

https://wordpress.org/plugins/usagedd (simple, shows speed/number of queries when in a Page Builder editor)

Conclusions:

It’s likely that you now know less about databases than you did before you came to this page. That’s a measure our understanding of databases. They’re weird things and, honestly, most of it is Voodoo.

Unless you know what you’re doing, stay away – there be monsters!

Let’s hope that when we get to the next letter… ‘E’, we know a little more. Hope is a good thing, if not always well placed!

Nathan writes posts and creates audio about WordPress on WP Builds. He can also be found in the WP Builds Facebook group.

The WP Builds podcast is sponsored this week by…

Cloudways WordPress Hosting

and

AB Split Test – The fastest way to create Split Tests in WordPress

and

The WP Builds Deals Page

We thanks them for their support of WP Builds.

Transcript (if available)

These transcripts are created using software, so apologies if there are errors in them.

Read Full Transcript

Nathan Wrigley: [00:00:00] Welcome to the WP Builds podcast. Bringing you the latest news from the WordPress community. Welcome your hosts. David Waumsley,and Nathan Wrigley.
Hello there and welcome to the WP Bill's podcast. Once again, this is episode number 216 entitled to D is for database. It was published on Thursday, the 11th of February, 2021. My name's Nathan Wrigley, and I'll be joined in a few moments by my good friend David Wamsley, so that we can have our database discussion.
But just before that, a couple of bits of housekeeping. WP Builds produces quite a bit of WordPress content. Each week. We do a podcast on a Thursday. That's what you're listening to now. And we do this week in WordPress, a Roundup of the WordPress news. It's like a newsletter mixed with a live show with some notable WordPress guests.
If you want to join us for that, you can go to WP Builds.com forward slash live. Or join us in the Facebook group. WP Builds.com forward slash Facebook at 2:00 PM UK time. Join in the comments. It's a very lively and interesting discussion, and we do it every single week. If you do want to stay up to date with all of that content, head over to WP Builds.com forward slash subscribe and sign up on the forums.
Join the Facebook group, bookmark the YouTube channel and so on. And other pages, WP Builds.com forward slash deals. It's a searchable filterable list of deals in the WordPress space, significant amounts off lots and lots of plugins. And those deals never seem to go away. WP Builds.com forward slash deals.
And lastly, if you would like to advertise your product or service in front of a WordPress specific audience, head over to WP Builds.com forward slash advertise a bit like AB split tested. Do you want to set up your AB split test in record time? Like in a couple of minutes, use your existing pages and test anything against anything else.
Buttons, images, headers, rows, anything. And the best part is that it works with element or Beaver builder and the WordPress block editor. You can check it out and get a free [email protected] Okay. Let's get stuck into them. Main portion of the podcast, David Waumsley, and I having a chat about databases.
Now, before we begin, I think we should make it very clear that both David and I would rather that databases didn't exist. We've spent most of our life in the WordPress space, trying to pretend it's not there trying to avoid it, but it's not possible without a database. Your WordPress website would literally.
Do nothing. So it's an important thing. However, there's a lot of tools out there which can help you interact with it. See what's in there and help clean it up. So the debate ranges quite broadly. Actually we cover a lot of ground, but obviously I think it's fair to say that we probably put our foot in it from time to time.
If we do let us know in the show notes, perhaps you can find the show notes on the podcast website or in the Facebook group. I hope that you enjoy the podcast.
David Waumsley: [00:03:15] Hello. It's the fourth in our series of chats called the a to Zed of WordPress, where we attempt to cover all the major aspects of building and maintaining sites with WordPress today, we're going to really set pulse is racing with the letter D which stands for data base.
Nathan Wrigley: [00:03:35] I don't know if it was pulse. Is it going to be racing for this? But
David Waumsley: [00:03:39] I don't know. Just the word sounds so incredibly dull. That certainly for me, for years of using WordPress, I just pretended that database didn't exist. It was this little cylinder thing that is the icon showers that just happened to do magic in the background and they just avoided it at all costs.
Nathan Wrigley: [00:04:01] You know what? I think that's a really normal thing, because if you think about WordPress, you just think about the visual aspects to it. You think about the UI and the theme and the content that you're putting in and the texts that you're writing and the images that you're uploading.
And really, if you were a cursory user of WordPress, it would be entirely. Understandable. If you never knew there was such a thing as a database, if you were to ask somebody who just is a consumer of WordPress and doesn't really have any technical insight into it and you ask them, where's the data stored, they probably just say it's in WordPress, isn't it.
And Noel. Okay. What does that mean? It's in, it's on my website. It's in WordPress and I have to say. This is not an area where I feel in any way, an expert. This is something that I have largely ignored the beauty of solutions like WordPress is the fact that it is very possible to not get too involved in the database and manage to use it quite successfully.
But I know that your you're more of a more adept than I am, but I don't think you would claim to be an expert either. Would you.
No, no way. I still really don't understand the basics. And I don't like to fiddle around with the database. I have come to appreciate how useful it is to have some knowledge about it or.
To even figure it, the fact that it's there when you're building your sites, because it makes knowing something about it makes such a difference to the general health and the speed of your site. That it's something I can't ignore any longer. So we could talk a little bit, do you have any. Do you have any database horror
stories?
I really don't. The only sort of connection I've got with databases is when I was learning PHP right at the beginning. And again, I would stress that I never really went enormously far down that journey, but, 2020 years ago, or I don't know what the number is, but a number of years ago, you get those PHP primer books and you go through and you get the basic instructions of how to do simple commands.
And then chapter two or three is up, an introduction to the database. And so that's where I went and And so that's really my understanding of it. But to say that I've had a a dilemma or a drama with the database, no, I really can't put my finger on anything where something has gone catastrophic really wrong.
Now it may be that the database was. Behind certain problems and a simple backup restore has fixed that problem for me, I'm not entirely sure, but no. And I would just add that maybe that is the point of systems like WordPress really is to abstract the boring, difficult, perhaps potentially uninteresting bits of.
Creating dynamic websites with a CMS like WordPress is to just abstract that and take the database problems away to make it so that you really don't have to have that technical understanding. You can just type. Drag in an image, click save or publish and you're done. And I would imagine I would be very short to say that the majority of people using WordPress have no experience using a database at all.
So no, no horror stories gone. You must have one.
David Waumsley: [00:06:59] A few and I didn't like you. I didn't read any books in the first place. I literally have home only needed to worry about databases because of some issues. And one of them was quite recent within the last year, perhaps where I had a multi-site.
Which I put out, which spun out different versions of a site for people who wanted to test stuff with Beaver builder. And it's, I was wondering what was happening on my server, cause it was getting full and I was thinking it just can't be, and it turned out there was 20 gigabytes. In this one multi-site which had been used cars are cleared itself up.
So that was an incident where I suddenly had to work out how to clear up a database because it's was just so big and it was getting so slow, but even going further back. But I guess the first time I felt I needed to understand a little bit was because I, the only site really that got hacked was one.
When. Before I started doing this as a profession. I ran the shop and it got hacked and they clearly, I couldn't clear up the files. Clearly it's done stuff to the database because they just kept gaining access all the time. So it's really the only reason I. Come to appreciate databases just because of issues.
I've got another one as well, going on at the moment, which is I use server pilot to manage our servers, which are DigitalOcean. And interestingly there I've got an issue that it's, the database is not up to date with what it really should be for WordPress now. And their advice on server pilot is to, unless you really need to don't update.
Yeah. So it's not a one-click solution, you have to run some commands to do things. Yeah. So these things have intruded on me really. That's my stories with things I've had to. Get involved with the database only because an issue has come up. Yeah. Yeah.
Nathan Wrigley: [00:08:51] We do have a fairly a broad audience and perhaps it would be apropos at this point to say that when you are using WordPress, what a database is, and really WordPress comes in two parts and one is one is the files.
That you upload to your server. And, if you download WordPress you are downloading the files and they're all of the, the PHP files and so on and JavaScript and whatever else it might be. And you upload those to your server. But that by itself would be completely useless. If you've tried to use a WordPress website where there was no connection to a database, literally nothing would happen.
And so upon installing WordPress for the first time you are asked to. To create and provide access to WordPress, to a database. And it's maybe that you've never done this before. And it essentially, most web hosts will create. On limited amounts often of databases for you, and it's got a username and a password and a user, and you supply those to WordPress and suddenly it can start storing things that you would like to be maintained from session to session in it.
But that's where it becomes confusing because what. Is it, what does the database look like? I can see WordPress. I can see what it looks like. It's got that black bar at the side and the buttons all look like this and there's this, I just get it. But having an insight in what a database looks like, I find that really difficult because you never really get to see it got no visual representation, unless yeah.
You use things like PHP, my admin, which provides a sort of. How to describe it, maybe like a spreadsheet way of viewing the database but by itself, I know people are quite happy with the command line interacting with the database that terrifies me.
David Waumsley: [00:10:35] Yeah. Yeah. It does be I've been my only interaction again is through PHP.
My, and in fact, now I'm using cloud host and I have to install a plugin to be able to do that as well. So I installed WP PHP, my admin plugin, so I can interact. Cause I don't have that through my management system.
Nathan Wrigley: [00:10:56] Oh, and does that then provide the reliable UI that we're all used to it, it just looks like, okay, that's interesting.
Often that's provided by the web host, isn't it? You'll return to your control panel or whatever you want to call it. And there'll be a button there for PHP, Miami, which again is slightly confusing because it's not really. PHP. Is it that you think you're administrating at that point?
You're administrating the database. Anyway. Yeah. W that's what it is. WordPress is a database to store the stuff that you put in to WordPress, whatever that be and files. Which also stores the stuff that you might put in like images and so on. If you're talking in text and saving options and, modifying things, that's all often getting saved into the database, which is a difficult thing to imagine.
David Waumsley: [00:11:42] Do you know what? I think in some ways it seems to me that. The way that most plugins and themes are marketed in WordPress, these days are very much on speed. And I think if you're really taking a holistic look at speed, you can't really ignore the database then because of the way that WordPress works, it's querying the database all the time.
So to ignore. The query of the database is to ignore a key part of the speed. Now that might not show itself necessarily on the front end, that we were talking about Cashin. The last time we were talking and that could disguise it, but really it has a great impact, the number of queries that need to be made to your database.
To the speed, certainly at the backend of WordPress.
Nathan Wrigley: [00:12:29] Yeah. This is one of the things which people talk about all the time. Isn't it about if you install a multitude of unnecessary plugins, you're going to be slowing down your site and on the face of it, it might seem well. What, why would that matter? I'm not using those plugins.
Most of the time, I've got this thing over here and I'm never using that. I've got this thing over here, but The way it works is that often those things are creating clutter in the database. And again, straying into areas that I'm not really qualified to talk about. But my understanding is that the more clutter that there is in the database, the more difficult it is to get the data out that you're trying to pinpoint.
And an often things are being done that are unnecessary, that really don't need to be done. And in this case you might be adding five or six milliseconds here and a quarter of a second there and so on, and it all adds up.
David Waumsley: [00:13:19] Yeah, absolutely. We're confused still aren't we, and somebody might be able to tell us, but certainly my experience with the 20 plus.
Gigabyte multi-site that I had, it really gone so slow in the back and it was there because it had in the database, the size of the database was huge, but what was being queried, wasn't those things that were in the database. So we knew, as soon as I cleared it up, it got so much faster.
And the only recently I was, yeah, Telling you, it wasn't. I was doing a bit of experimenting, loading up lots of different plugins and playing around with stuff. And I used a plugin called WP reset, which will reset your database, but it also allows you every time you installed a new plugin to automatically create a new snapshot of everything that's gone before.
So very quickly, this fairly simple site I was working on went to eight gigabytes and I just felt it getting so slow. And I forgot that I put the snapshots on. So I cleared them all up. And just went quicker. Now, all of these snapshots, the stuff that's in the database here is not actually being used, but it was still slowing down the site.
Yeah,
Nathan Wrigley: [00:14:29] it would be really interesting if somebody who's got a deep understanding of how databases work specifically in that case can explain to us how it's possible that in your case, these WP reset snapshots building up to eight gigabytes You, how on earth that slows things down. I suppose that it's a bit like on our computer, isn't it?
If you've got a full hub desk that tends to slow things down, presumably it's just that the computer itself has to retrieve the bits and find its way amongst the detritus to get to the bit that it needs. And if there's more detritus that is not. That's getting in the way of what the database needs to give out.
Maybe that's, what's cluttering it up, on the face of it, it sounded like those WP reset. Eight gigabytes shouldn't in any way, get in the way, because they're not being used, your experience is quite the opposite. So I would like to know the answer to that. I'm sure we could find it out with a quick Google, but maybe if somebody can let us know that would save us a quick kill
David Waumsley: [00:15:26] it.
Wouldn't how significant is. So I think it's hard to I think it's definitely worth looking into these things. Something really only over the last. A couple of years, I guess maybe when I first started getting serious about making websites, I I used to try out a lot of theme, forest themes, which are very heavy and bloated, and I didn't understand speed very much.
Then it was all about caching and what came out the front end. I didn't realize what was happening to my database and how many PHP functions needed to run was going to impact on it. And eventually I changed my approach. I went from more simple setups, but then I started to look at. Things, but I've ignored that.
And then as we moved into page builders, I've, then I've got back into the habit of using plugins to, to measure kind of database queries. So things like query monitor, and there's another really simple one usage Deedee, which just gives you some basic idea of how many queries are running with the, the plugins that you've got in your install.
And I find that really helpful as well. Starting to build sites just to see how bloated they could be getting. Are
Nathan Wrigley: [00:16:34] those plugins useful because I've not got expensive either of those ones have those plugins, are they fairly intuitive from the get go? So for example if somebody like me was to install it with very little familiarity with how this stuff really works, would I get a decent understanding right off the bat and understand what it is that was unnecessary.
David Waumsley: [00:16:55] Yeah, you would definitely, usage, DDS really simple. All it does is put a little bar on the bottom of your site, even if you're in the page builder, which is quite nice where it gives you four figures and two of those are database related. They tell you how many. Database queries are being run on that page.
Whether it's the front end or the backend office, it's going to be different. And the front end office is the one you're going to worry about, but it also gives you the process in time of those databases. Now they're not a hundred percent accurate because there'll be impacted by your hosted and other things, but it gives you some indication of how things are changing and.
I've known this for a long time, but I've ignored it until recently. There's this is general rule of thumb, which I've seen mentioned before, but it's definitely mentioned on the repository page for usage, DD, where it's expected. If you're running something like 150 queries on any particular page, you may have an issue with your theme or plugins.
And I find that quite fascinating because it's quite low because. You've really you've you pursued, you put WordPress on, you're going to have 23 quavers ready in any way. So you've not got a, a great deal of room. And I would say with most page builders, it's phasey to go over the 150.
Nathan Wrigley: [00:18:10] Yeah. Oh, that's really interesting. Just give me the names of those ones again.
David Waumsley: [00:18:15] A query monitor. Oh,
Nathan Wrigley: [00:18:17] I haven't used that one. I tell them I have used. Yeah, but the other one I've not come across. What's that one
David Waumsley: [00:18:22] usage D it's just like a, it's like a cutdown version of it where it just gives you the basic kind of speed for speed parameters.
Radius gives you a sort of load. The, just looking at the database queries is really interesting. Yeah,
Nathan Wrigley: [00:18:36] it's it's the sort of thing that I used to have religiously on my desktop. I would constantly be, wanting to know what my computer was doing at any one moment in time in a sort of vain attempt to try and on install things that were slowing it down the principles, the same, just get an idea of what's being used.
And so have you made any decisions based upon the data that's given back to you about what you thought. It was, I don't know, let's say slowing it down in a way that justified on installing stuff or you thought that's, there's a problem there that plugin might've been poorly written, so I'll install it and find an alternative, anything like that.
David Waumsley: [00:19:10] Yeah it's helped me to audition some things. Certainly that and a little bit of looking in sometimes when I do look into tables to see how many extra tables have been created by a plugin. And also whether that plugin clears it up, it's mess when it's deactivated. That's also a key thing, but I tell you what, it's got more confusing for me recently.
In fact, again, it was only. Last week or a few days ago, I think that I contacted short pixel because I spotted with using these that were short pixel on it was adding something like 70 on my site, 70 plus backend queries, 50 on the front end one short pixel, which just optimizes images for me. I thought, what the heck?
So I sent them a message saying. Yeah, this worries me a little bit, but I will say also that I could notice it in speed. And they've actually written a little article on it saying that actually you only get those. If you've got the role that allows you to mess around with short pixels. So anybody else doesn't have that generated?
So as is if I was in sales and an editor, or just a writer, an author. I wouldn't see those, but also that they were very quick. So what I've realized of course over the years is that queer is, there are a lot more being made anyway in WordPress. But they're not all equal. Some of them were very efficient, so short pixel didn't slow things down at all, really, as far as I can see.
Okay.
Nathan Wrigley: [00:20:38] That's a good point to notice as well. They're not all equal the fact that some are devilishly hard to achieve and some might literally be just pulling some specific thing, which is really quick to achieve. Yeah, that's a good point. Should we dwell on the the cleaning up thing that you mentioned earlier?
Because I think this is probably something that's really important in this discussion. The idea that. If you install something and then at some later point you decide to remove or deactivate and then delete a particular plugin. The idea that it, it cleans itself up again. So again, just for those people who are not that sure, when you install a plugin, it will set up.
Some some aspect of the database and you were saying to me, before we started the call, increasingly it's setting up its own sort of custom tables, but often it might be putting things in metadata of the things that are already in WordPress. Let's just say it like that. Yeah. And the hope would be that if you've decided upon not needing a particular plugin anymore, that it would go in and search for the things that it created and remove them, expunge them from the database.
But that's not what you found or what is always the case, is it?
David Waumsley: [00:21:46] No, it's not. And we were just talking before we think that it would be nice if there was an extra option in WordPress, but probably not achievable, but yeah, with a lot of the plugins, I think. And throw other people out there. I know who share the same beef with it.
If there's a plugin that puts something in the database and doesn't clear itself up, it does annoy. And a lot of them. I think do clean themselves up. Typically things will stay in place if you've deactivated the plugin, but when you delete the plugin, it will then remove all this instances from the database.
And some of them something like word, fence, it is a setting which isn't on by default, but you can turn it on. So when you deactivate, it will clear out all of its database. And I think that should always be there, but it's not with them all. And, There's no rules about what goes on the repository when it comes down to that kind of thing.
So you would have to check for yourself. Yeah, it
Nathan Wrigley: [00:22:43] does seem like a sensible default. And again, I suppose we're talking about the heritage of WordPress here. Aren't we, when you designing WordPress go back 15 years, there was probably no understanding at that point that people would be installing dozens and dozens of plugins.
It was just a simple blogging platform just to reiterate what everybody says about the initial. The, the origins of WordPress and there was probably very little need for this kind of stuff, but I think that would be it. Yeah. An interesting option to explore, perhaps again, somebody could illustrate to us why that's just stupidity, but having the option to deactivate then delete the plugin, but maintain the database would be a nice option, but also the option, like you've just mentioned with Wordfence too, to delete everything.
And because there are no rules, my guess is that. Plugging authors or theme authors or whatever it might be would need to we need to go the extra mile to create in the code to go and successfully remove itself. And if that's not a requirement, perhaps that's a step too far for some of these authors.
David Waumsley: [00:23:48] Yeah. Exactly. Most people. Ignore that database. They don't worry about it and it doesn't help you to sell your product. Does it? You're not, you've got to be a really good person, haven't you to think about these things? But again, maybe there aren't any rules that can ever apply to this, because say with it's one of my gripes when plugins don't clear up after themselves, but use it's the type of plugin that won't be part of my mainstay.
It'll be something that I'll try out or something and I'll want to swap it with another version of the plugin that does the same thing. I probably. Let off page builders for nightclub for themselves.
Nathan Wrigley: [00:24:28] So how do you discover that a plugin has. Left traces of itself. So most people's experiences that you click the activate and click delete and you wait for a few seconds and it's gone.
Do you habitually go through like a testing site just to see what's what the residue is after you've done this with a plugin that you've tried out and therefore make the decision. I shan't use that just for that reason alone.
David Waumsley: [00:24:52] Yeah I picked up these things along the way. You hear people say about this and then you go and check and you realize that your database has got bloated and then I've taken a look, but I've started to make it a bit more of a routine that when I.
Introduce a new plugin. I will use this WP reset. They're getting a lot of advertising out there. Because what it will do is with the pro version. Anyway, it will give you a comparison. It will tell you what new tables have been added and taken away. So you do get to compare. So if I'm trying out some new themes and plugins, I'll get to find out through that.
Yeah. No, I don't do any of these things as routine. I've just realized that certain plugins will leave stuff just when I've been looking in the database. So it's random, but I've started to be a little bit more strict now. So I auditioned plugins and I would use something like WP reset.
To check a reset because the nice thing about pro version of that one is it will give you what has changed in your database from one version to the next. So I can see if something, what has changed in the database, but that's only a very recent thing that I started to look at that. And that's probably more because more recently I D there's more products.
I just want to try out because, I do some content, so it's not what your average user's going to do, but yeah.
Nathan Wrigley: [00:26:09] Yeah, it does seem like the sort of thing that would be a good sales pitch on our website. Maybe not to the majority of users because they really wouldn't be that interested, but for a proportion of users, especially people like yourself who are interested in this, it'd be a nice little bullet point in their sales pitch just to say.
We N we on install hour or, we drop our tables or whatever it might be. Upon deactivation, we clean ourselves up. We're good custodians of your database, because that is a thing. And it wouldn't be something that would really draw me in, but it would you, so it might be, there's some sort of difference between making a sale and not making a sale.
David Waumsley: [00:26:47] Yeah I feel a lot of the marketing that goes on with speed at the moment tends to be a little bit misleading. They'll tend to focus on Scores really speed scores and stuff like that, where a lot of what's really going to improve. The performance is going to be, those kinds of things clearing up your database, the number of queries it does.
But I guess the people who want to focus on speed probably are the ones who don't want to focus on that stuff because often they're selling something, which is all very with bank, does everything for you. And they tend to be the things that. We'll run quite a lot of queries, so starting their interests.
But yeah, I do think, but that the long standing WordPress professionals, the people who have been making plugins for free for the community for years, tend to do these things as good practice. Don't they?
Nathan Wrigley: [00:27:36] Yeah. You were mentioning earlier that that WordPress has 23 queries that it runs to start with.
And obviously there. They're out of the box, there's nothing much you can do about that and data. Those are the tables that you get when you install WordPress. And you also mentioned, or maybe it was me that mentioned that there seems to be a bit of a move recently toward storing data in tables, specific to that plugin.
Have you noticed that, is that something that you've noticed and does that make life easier? Yeah, you're able to track down those things too, to on install after the fact, a lot more easily.
David Waumsley: [00:28:10] Yeah, so much easier. Isn't it. If you know that this is a table related to that plugin, and you can just delete it and be gone, if you haven't to delve into your options table, where almost everything goes in WordPress, it becomes a much, much trickier thing to do.
It was very easy for me to use a plugin called advanced database cleaner, which is I could use the free version on the repository to clean up my multi stores because they, each of the individual sites had its own prefects. So it's database, so I could just find them and just I'm going to delete from here to here and get rid of it.
So it's easy. So I, I avoid a lot of this, but yeah, it's so much easier if things are kept separately,
Nathan Wrigley: [00:28:49] I'm not a hundred percent sure if that's the, one of the primary reasons why this has been done. I imagine it's been done for ease of accessing the data and, and so on. But yeah, it'd be interesting to know if there is that as part of the reason why people do it more and more, because you can just find the.
Plugin X database table, and then just remove it safe in the knowledge that is isolated from anything else. And you're not accidentally going to delete something because I think that's the fear that I've got with all of this stuff. And you mentioned WP reset a little while ago, those kinds of things they do.
Concerned me a little bit because I'm trusting. I don't know if in your case you take the information that comes out of WP reset, and then you click a button within the plugin to clean things up, or if then you go manually and hunt for the things that it suggested and remove them yourself. But I just fear accidentally because I'm so novice with all the sort of stuff I fear accidentally clicking a button.
And removing something that I ought to have removed, and then I'm stuck because I can't figure out what the way back.
David Waumsley: [00:29:53] Yeah, but you've got backups other than you. And I think that's, yeah, I do that. That's how I've managed to go at things. Cause I don't know what I'm doing half the time. I should clarify something.
Actually. I said about the default 23 queries with WordPress, that is assuming at the moment that you've installed the default 2021 theme, which has to be. Yeah. Some of those queries as well. So probably if you weren't running a theme, it might be lower. Okay.
Nathan Wrigley: [00:30:20] Yeah. That is interesting though. I do like the idea of having a plugin that sort of takes care of that for me.
But again, it just, at the moment, it just. It just concerns me that I'd delete something that I then, essentially I'm trying to save myself a bunch of time. The, yeah, yes. I could from a backup, but I lose quarter of an hour, half an hour or whatever it might be. And most of the time I just can't, I can't cope with that.
I just don't want to lose quarter of an hour or half an hour. And so I, I firmly just carry on regardless with a polluted database, which is a bit stupid.
David Waumsley: [00:30:52] I don't understand what's going on actually with a lot of the plugins. I couldn't, there was a big update to the Yoast SEO plugin version 14, where you had to, they changed the whole layout of their tables as well.
Yeah. But it wasn't, in the way that I'm it seems to be something where they've got their own tables now, but this was much more complex about interacting with the rest API end points. And at that point, when I'm reading the article, I've just completely blanked out. My eyes have de-focused and I look for words, I understand, like at the pretend I've read it, there are a lot.
Cool. Yeah. That stuff's going on. I think it's going on in woo commerce. Cause you'll notice now with an update, which is a bit of a pain, to be honest, if you update, like we do with something like main WP, you think you've updated, but you haven't because it's a WooCommerce database to update almost with every new major release.
Nathan Wrigley: [00:31:47] Yeah, I've had that with a few plugins recently. I won't bore you, but one of them is is a podcasting plugin and they've obviously done something similar and it required a simple app in our room, but doing a similar thing the other day as well. And I can't remember what plugin that was, but that it needed to update something in the database.
So in that situation, I would just happily clicked. Yes, because I was in the site anyway, but I don't have no idea how long that nag was there because I was. Updating it via main WP. And I logged in, I can't remember even why I looked in, but I logged in and there was this nag saying, you need to update the database.
I did it. And it literally took the length of time. It took me to blink to update the database. So I've no idea what it did. And the nag went away and I thought, okay, is it still working? Move on. And again, it's just another example of my ignorance of these things. I don't even know what it was doing.
Just trusted it to do it. And. Yeah, but you're right. I would have missed a lot of those things. Had I just been using main WP to update every time you've put a list in our show notes, have a ton of tools. Now, I don't know if you've got experience with all of these, but you've got, Oh, I don't know.
There must be a dozen tools in there that you wear that you've come across to manage databases with Jonah. Would it be worthwhile going through some of those maybe.
David Waumsley: [00:33:01] Yeah. I've mentioned one of them, which is WP PHP, my admin, which if you don't have access to that through your hosting, you can get, and I've used that few times.
I think it's a little bit risky with the security using that, but okay. And I mentioned advanced database cleaner, which is really good for. I think that I've not used the pro version, but I managed to get away with that. That's really good. If you need to do that certain things, there are some plug-ins and I'll give you an example, mal care, where they do.
So monk has got a free repository version for doing security scans. Really good deal. I think it's really excellent. But if you go to the pro version, which I have done, and you want to revert back for free, you have to go and find it. Their tables to delete the information they kept. Cause you can't be vert back.
And the kinds of things like that. And I've been able to do that with things like advanced database cleaner that allows me to get around and find stuff easy. So yeah, for me, I don't know if you use this at all because your main WP user, the maintenance. No, I don't.
Nathan Wrigley: [00:34:06] And so I saw that and I know it exists, but I either don't have it installed on my.
Install of main WP, or I simply ignore it. I want to know what does it do?
David Waumsley: [00:34:18] I trust that he does well, I think it does really you set it to do a cleanup or an optimization. Every whatever time you set it, I set it for once a month and it's supposed to go to optimize your tables, but I don't exactly know what that means, but it just clear up and you can set this.
To however you want. So you can clear it to get rid of your deleted spam comments or your revisions or all of those things that could you know slow down your site. So I like that it just clear stuff up once a month. Ah, okay.
Nathan Wrigley: [00:34:48] So it's cleaning up fairly benign stuff for you, stuff, which is clearly a spam, a comment which is still left in spam is an easy win.
Isn't it? And things like that, which just data, which you. Categorically don't need that. That seems like an easy win. No, I must do that. If that's the case, does it do anything which gives you palpitations? Does it attempt to do things which might concern you.
David Waumsley: [00:35:13] No, just that, I find, I think about the, what clients might be doing, because if it's going to get rid of revisions, I don't know if they might be working on stuff.
So I've set that, not that Sikh keeps, I can't remember what I've set that on, actually, to be honest, but anyway, there's been no issues, but similarly I've also started using WP rocket, which does the same thing. You can set that. As well to do a similar cleanup. So I've actually got twice a month.
I've got WP, rocket doing one of them. I've got main WP doing another one.
Nathan Wrigley: [00:35:43] Oh, interesting. That obviously WP rocket is all about speed. You've got to assume that the cleaning of those unnecessary things is in some way impacting the speed. Otherwise I don't suppose they'd be getting involved in it.
David Waumsley: [00:35:57] Do you know, there is one other thing. You mentioned this before we're talking about GDPR. I think it's a really good thing for us because a lot of the plugins that would leave stuff like gravity forms, it might case now they've got settings where you can automatically clean up. All the things that are being sent into you that go into the database.
And it's true. I think of many of the others as well, I think a Ninja forms and I did spot as well at fluent forms, which you've started using also added this as well. So you can clean up your database a bit there with those plugins. Yeah,
Nathan Wrigley: [00:36:31] the, I think that's a really important thing going forward because obviously privacy has become a concern in the last let's say.
Four or five years in a way that it never was. And increasingly, everybody's sending data across the internet all the time and there must be on many sites, completely unnecessary data, personal data that you're holding about other people, unless you remove it. And so the cleaning of that.
Database or those database entries, it might not just be fun to do or worthwhile doing. It might be totally legal legal requirement to do it. So that's a really good point.
David Waumsley: [00:37:10] Yeah. Yeah. I think it's helpful. And there's lots more plugins that need to do that wouldn't have done before they would have just kept storing the data endlessly.
So perhaps, other appointments. Type plugins, those kinds of things, but you can get them all to clear up now. So I think it's I think GDPR has been really good for clearing up databases in WordPress. I think because now there's more options. The rest of the things you mentioned but I put on this list here pretty much all doing the same thing.
They're all. Various things on the repository, WP optimized, WP sweep. They are both plugins, which pretty much do the same thing as my maintenance extension. Does they allow you to just clean up kind of transients and revisions and all of that kind of stuff.
Nathan Wrigley: [00:37:53] Okay. And I should probably say I will just copy and paste all of those links into the show.
So that will be all ready for you. We you don't have, we're not making any claims for what they do or necessarily saying, which are favorites, but I'll just put them all in and you can go and explore them. There's an awful lot on the on the repository isn't there's loads. You've got four or five there.
That's great.
David Waumsley: [00:38:14] Yeah. And I have tried them all over the time and I really can't tell them apart. In fact. Yeah. In fact, Leicester gamers, Chan, who is his name from when I first started using WordPress, I used his WP database manager and he also does WP sweep as well. So I'm not quite sure the difference between those two, but yeah.
Okay.
Nathan Wrigley: [00:38:36] That, both that, and we'll we'll mention them both. What have we missed? What have we missed?
David Waumsley: [00:38:41] The tool, which is a bit different, or there are another set tools for managing your database, which are things like a better search and replace or better search and replace that's around as well.
Which allow you to. As it says, search and replace certain items in your database,
Nathan Wrigley: [00:38:58] have you used no. And I've definitely heard of it, but I'm struggling at this exact moment in time to think of a use case for it. What exactly you're trying to achieve by searching and replacing things in the database.
Isn't just, ah, isn't that just changing one thing for another.
David Waumsley: [00:39:12] Yeah it used to be part of my method for going live on the site. So I'd have that on the dev site and then I'd want to write, so I transfer it over and then I'd use better search and replace to just change the database to the new domain name.
Yeah. But that's mostly how I used it. Started to fail on me that, so these days I tend to use something else to just move the whole thing in one go, but yeah. Yeah, there are all those tools there
Nathan Wrigley: [00:39:36] as well. Okay. Yeah. I confess I don't use those. Whenever I'm moving aside, just log into, PHP, my admin and just find that particular entry and strip out the dev or whatever it might be that's required.
So yeah, that's the way I do it as well. That's Yeah, there's a lot of links. Definitely go and check the show notes because David's compiled a fairly impressive list that he's tried over the years. And just shows the difference between us. Cause I've not really played with any of those whatsoever.
Seemingly entirely happy to let Maya databases swell over the year. Yes. But to be fair to me the sites that I've been building, they are largely static. The only changes that are in any way likely. Either maybe stray comments, like you said, spam that somehow got in or no, just little updates that have come through things being updated.
What have you, but in most cases, the sites or the sites are static and once launched, then it really change over time. So I don't really see the database growing. I often look at the stats of what the disc usage is. And in most cases it's not really changing much over time. So I'm fairly sanguine about that.
David Waumsley: [00:40:43] Yeah, I think, you know the database now it's only looking at what fills it up quickly because I do notice that speed difference. There is one other thing we didn't mention here because you've got two options of new for WordPress. You can love to go the, my SQL way or you can go with Benbria database.
Nathan Wrigley: [00:41:00] Yeah. I've always selected my SQL I'm my understanding is that there are there are benefits to using Maria DB, but I've never. Never really used that. Yeah. Largely my impediment to doing it is because it's just what I'm used to. I'm used to installing my SQL and I'm familiar with it. And so I haven't really changed, but so you say speed.
Have you got experience with trying it and getting benefits from it?
David Waumsley: [00:41:27] Nope. Have tried it. In fact, with some hosts it's easy to swap. I think cloud ways allows you one clicks to swap over from one to the other. And I have definitely transferred from one to the other, through different hosting without any problems.
So that's quite nice thing. And your small little issue, which may have just been nothing to do with changing the databases are day experience. So you can go from one to the other, but apparently Maria database benefits on the speed. It comes later and it's more performance, but I think my SQL is the kind of safer, better.
It's been around a longer time as an it's got more people behind it.
Nathan Wrigley: [00:42:04] Yeah. It'd be interesting to see over time, which one? Which one takes the lead is Maria DB sort of a fork of my SQL. Do you know?
David Waumsley: [00:42:13] I'm going to go into making up stuff now, but as I understand it, as I remember the people who set up Maria de B were the people who started off my SQL.
Okay. Okay. Yeah, so it's, they just branched off and did this all at some of the people who started that. Yeah, that's my understanding.
Nathan Wrigley: [00:42:29] Okay. Yeah. It's again, a Clarion call for anybody who's listening to this, who would like to tell us what we should have known before we started recording this podcast would be, it'd be most welcomed.
It'd be interested to know what the reason for that is. I'm sure there is a reason don't suppose you'd fork and open source projects, unless there was actually a purpose. But there we go. Have we covered it? Have you said it say yeah, I think so. That was a, that was
David Waumsley: [00:42:56] a lot. That was one, one other thing I'll just throw in there quickly because I've experienced this now.
You can. Host your database somewhere else independently. And I've always wondered about this because like we were with a host who did that and I. I'd love to read it. It's just a question there for anybody who can tell me the benefits. I saw the negative side of it, because what I found is that I would use the kind of tools I would to check whether a site was running live and it would say it is.
But in fact, with Cashin, it was an illusion, the database server gone down. And I didn't know that people couldn't contact us. So I've only seen the negative of separating the hosting of the database and the files, but right.
Nathan Wrigley: [00:43:38] I'd love to know the only time I've ever really. Played with that. But I know that a lot of people do because of speed benefits and, dedicated infrastructure for databases, as opposed to files, the only time I've ever done that is when I'm moving a site and I keep the database somewhere, we'll move the database first and then, make sure that it's all connected, then move the files and make sure that it's, that's all connected and so on, but I haven't really any benefit, but I know for things, things like Amazon and what have you, you can have.
You can have a a different location, but I'm just on more traditional normal host hosting where the host takes control of all of it. And I'm not saying things up separately.
David Waumsley: [00:44:17] I think you've answered my question, I think yeah. In circumstances, perhaps with really heavy use with commerce or something that might be a real benefit to give yourself some yeah.
Yeah. That's a
Nathan Wrigley: [00:44:27] goddess. I don't know. But again, for the 50th time in this podcast, let us know what we already should have known. So there we go. I think we've done it. Databases are done. We've got email in a couple of weeks. Do we know what he is? Let's wet to the appetites of a
David Waumsley: [00:44:43] four. Ecstasy you were suggesting.
Yes. Yes he is. No it's for e-commerce.
Nathan Wrigley: [00:44:52] Yes. Again, another subject of which I am completely unfamiliar. Join us in two weeks time when David will talk about e-commerce and I'll just tag along for the ride. Now that was a good debate. Thank you. Not a debate. That was a good discussion. I enjoyed that.
Thanks David.
David Waumsley: [00:45:07] I did. Thanks. Bye. Bye.
Nathan Wrigley: [00:45:09] I hope that you enjoyed that. Always very nice chatting to David about these things, and I'm sure you can agree this week. We really did Excel ourselves with our ignorance. It's a subject for which we really hold our hands up and say, we don't know an awful lot about, but it's nice to discuss it.
We air the things that we've used to make it slightly easier to interact with the database. If you've got any thoughts on it. Please head over to WP Builds.com find episode number 216, and post a comment there or head over to the Facebook group, which is WP Builds.com forward slash Facebook. And you could put a comment in there, let us know what you think.
The WP build's podcast is brought to you today by AB split test. Do you want to set up your AB split tests in record time? The you AB split test plugin for WordPress. We'll have you up and running in a couple of minutes. Use your existing pages and test anything against anything else. Buttons, images, headers, rows, anything.
And the best part is it works with elements or Beaver builder and the WordPress block editor. Check it out and get a free [email protected] Okay. We will be back next week for another podcast episode. This time, it will be an interview on Monday. We have the live WP Builds.com forward slash live. It's called this week in WordPress.
Join us. You might enjoy it, but if we don't see you between now and then I hope you stay safe and have a good week. I'm going to fade in some very cheesy dance music. I'm going to say goodbye.
Oh,
David Waumsley: [00:47:25] Yeah.

RECOMMENDED STUFF

These are affiliate links and the small amount of income we derive from affiliate income allows us to pay the bills and keep the lights on