On today’s episode, we’re gonna talk about how MySQL interacts and works with WordPress right here on Your Website Engineer podcast, Episode No. 295. Hello everybody. Welcome back to another episode of Your Website Engineer podcast. My name is Dustin and today we’re going to be talking about MySQL and how it works with WordPress and handles all of the backend things of WordPress. But first I’ve got a little bit of news for you, of course.

The big thing that’s in the news this week is WordPress Beta 4 for WordPress 4.6 is now available. And again it is still under development. It is still development software. This should be the last week it’s in Beta. This week it should go into release candidate state. So we’ve got release candidate 1, release candidate 2, and then it should launch in about three weeks or so. And so the Beta fixed about 100 more issues. There are 60 more changes. There’s been some emoji support. The input screen has been overhauled and it makes it easier for accessibility and making it much easier to install and run the importer.

They’ve added a media player via Media Element JS and that helps to fix the YouTube embeds that was broken. And the Ajax search for installed and new plugins has been enhanced to fix several accessibility issues as well. And so if you are a theme developer or a plugin developer, you want to check and make sure your themes and plugins are working well with WordPress 4.6 because, again, this will launch in just a couple of weeks. And so that is the news of the week. All right.

Moving right along to the “Is there a plugin for that?” section, there is a plugin, a great one, I want to talk about today and it’s called Duplicate Page. And this almost feels like a plugin that should be built right into WordPress core because it allows you to duplicate pages or posts or custom post types easily with a single click. So if you would go to your post page and you see a list there of all of your posts, when you hover over a specific post it gives you the extra menu items: the edit, the quick edit, the trash, and the view.

But with this plugin, it also adds one more menu item that’s called “duplicate this.” And when you click on that, it’s just going to create an exact replica of whatever you just clicked on. And it’ll keep it in the same state. So if it was a post that was in a draft state, it will duplicate it as a draft. And so it just works really, really nicely. It’s just a simple thing. Maybe you have a specific layout you have for a blog post, I could use this for each Your Website Engineer podcast episode. I could just say, oh, I just want to duplicate this and make a duplicate copy of it and then just fill in the information and keep the styling and keep the formatting.

And so it is a super easy plugin. It’s very, very lightweight. And it has more than 20,000 downloads. So if this is something that interests you, you can search for “duplicate page” on the WordPress repository or in your dashboard for the plugin section or you can go to the link in the show notes for Episode 295.

Okay, today we’re gonna talk a little bit about MySQL and just understanding how databases work. I’ve looked back through the archive and I think I touched on this just a little bit, on how MySQL works, but I want to just give a little bit overarching better summary and idea of how this technology works and what is MySQL and all of those good things. So let’s just start off with a definition. WordPress uses MySQL, which is an open-source, database management system, to store and retrieve all of your website’s information, from your content of your posts and your pages to your comments to your settings to your usernames and your passwords.

And just think of it this way. That a database is like a filing cabinet and MySQL is the company that made the filing cabinet. So the technology is a database but the company that created it is MySQL. It’s a popular choice for web applications, MySQL is. Joomla uses it, Drupal uses it, and a lot of high profile companies like Google, Facebook, Twitter, Flickr, YouTube, they also use it. Not necessarily to power their websites, but they use it in different manners and different ways.

So today we’re just gonna talk about how MySQL works and how it interacts with WordPress, including the database architecture, some storage engines, optimizing techniques, and some of the best practices for optimization in database management. So what is MySQL? Let’s dive into a little bit deeper of a definition. And MySQL is the central component of the LAMP stack. And so LAMP stands for Linux, Apache, MySQL, and PHP. And so this is generally the four pieces of technology that run on a server for a WordPress website to load.

You need PHP to be able to pull information from the database. You need MySQL, you need Apache, and you need Linux. Those are the four big things. WordPress uses PHP to store and retrieve data from MySQL using SQL queries within PHP markups. So like the underscore content or the underscore excerpt, that’s gonna go and it’s going to find that post ID. It’s gonna go and pull out all of the data that has been written in the editor for – so let’s just talk about the content.

So the content is a function within WordPress and when you use the content, it’s gonna go find that post ID, it’s gonna go and get anything that’s been typed into the editor window, and it’s gonna display that on the front end of your website. PHP and SQL work hand in hand which allows you to dynamically create that content. So that’s what allows when you go to a new page, it’s dynamically created because any PHP command will go and look for that information in the database, grab it, and then display it on the front end.

So this allows you to hide or show content based on specific users. So this is great for membership sites that you can say if you’re this class of member, if you’re an admin or an editor or subscriber or whatever, then you get to see this but if you are an admin you get to see this. So it helps to dynamically create that based on the username and the user type of the people that are viewing your website. I also want to note that you probably don’t necessarily need to learn SQL or try to figure out how all of it works if you’re running a small website. Then you probably don’t need it.

I probably was using WordPress for a couple years before I really understood how the database worked and how I could write SQL commands to do something in the database, to do it much more quickly than to go in and do it within WordPress. For example, if you wanted to go in and you wanted to delete – you could delete all spam comments. That would be a simple thing that you could do. You can actually do that with a one-line SQL query instead of going in and highlighting all of your spam comments and deleting them.

So there are some benefits and advantages of using SQL and you can do things much, much faster. If you wanted to do a renaming of something – like, for example, on each episode I have a little snippet before the “Is there a plugin for that?” that says “with more than 43,000 plugins.” Well, someday I’m gonna need to update that. Probably it’s outdated now. But I probably should go in and update that to like 45,000 plugins. And so I could write a SQL command that says search the databased for 43,000 and then replace it with 45,000. And it’s gonna do that across the entire website and across every piece of data that is stored in my database.

But that’s – you can also do this with plugins. So the cool part is WordPress has plugins that you can directly interface with SQL without having to know any SQL. So WordPress has a pretty straightforward database schema. It consists of 11 tables and their core components are in those tables and they cannot be deleted and removed. All of these ones, I’m gonna read through them real quick, all of them are prefixed with WP-underscore and this just is a default. When WordPress was first developed, it was always you just named it WP-underscore and then it automatically named these database tables.

Now with plugins like iThemes Security and different things, it’s best practice to actually rename these from WP-underscore to something random so that people can’t hack in and figure out where your database tables are. But for this example, I’m just gonna read them and that way you know exactly, by default, what these tables are called. So we’ve got WP_commentmeta and this stores any meta data from your comments left on your WordPress posts and pages. We’ve got WP_comments which stores all comments made on your site including published, draft, pending, and spam comments.

We’ve got WP_links, holds all information for link manager. And this was a feature that was in WordPress that’s no longer used but basically it used to give you the ability to put in a whole bunch of links for your favorite blogs that you like to read and consume and then you could display that as a sidebar on your website. And so this we deprecated in WordPress 3.5 and now it’s hidden by default with new installs.

WP_options is where a lot of settings are stored and your reading, your discussion settings, it has your site title in there. It’s got a bunch of information and some plugins will actually put information in there. And so it’s a good one to, if you’re ever looking for some sort of settings in your database, it’s generally gonna be in WP_options. We’ve WP_postmeta and this stores all the meta data for your posts and pages. We’ve got WP_posts which stores all your posts as well as pages, your navigation menu items. It stores your WooCommerce products. It stores custom post types. All that good stuff is in WP_posts.

We’ve got WP_terms and this stores the categories for your posts, links, and tags. We’ve got WP_term_relationship. And any posts that are associated with categories and tags, this is where that terminology and this is where these connections happen. We’ve got WP_term_taxonomy which describes the taxonomy as a category, link, or tag for all the entries in the WP_terms table. We’ve got two more. One is WP_usermeta and this stores any meta data from the users. And WP_users and this stores the user information for specific users, including passwords and things like that.

So those are the tables. You might notice if you go in and you look at your database that there could be extra plugins in there or extra tables. And that’s because plugins can use your database too. Once you install a plugin, it can use your database to store information. Maybe it puts it in that WP_options table or it can create its own custom table so that it can have all of the extra information it needs. See, if you put it in the WP_options table then it has to follow the same guidelines and has to put information in the same columns. You can’t add extra columns to a table that’s already there.

And so sometimes if you need extra information or if you just want to make it work a little bit more custom then you can create your own database tables and then you can say, okay, I need a table that has four columns and that’s gonna be all the information that I need. I want to lay it out nice and neat and really just organize it just a little bit better than just kind of hodgepodging it into the WP_options table.

Another important piece of information I want to share is the WP_query and this is a class that’s extremely powerful within WordPress that allows you to access posts in the database. And so it’s basically the main query that you use. You can go through and you can actually make a custom WP query so you can say that I want to pull out any posts that have been published between January of 2015 and December of 2015 if you want to have an archive page of just 2015. You can do a lot of really cool things. I’m not gonna go into a lot of the detail right here but just know that there is. If you search for WP_query in the WordPress codex, you’re gonna get a ton of information.

Another thing that I want to share about the database is optimizing your database. One of the most common reasons that a site is slow is a poorly maintained, non-optimized database. And so what that means is whenever you put rows in a database – so whenever you add a new post. Say, for example, you put a new post in and then you’ve added a bunch of these new posts and then you want to delete a few of them. Well, just think about them on an Excel spreadsheet and if you delete an entire row, it leaves a blank, an empty cell. And if you do that a lot then there’s just a bunch of these empty rows.

And what happens is MySQL doesn’t know, like when it’s going through the database, it doesn’t know those are empty so it has to stop and check each row for each ID and it takes extra time because it’s stopping at all these blank spots. And so when you optimize your website, you’re basically removing all of those blank lines. You’re kind of squishing all of the things together.

You can also optimize it to get rid of certain things, like you can use it to get rid of all your – like to optimize your database. If you get rid of all of your spam comments, that’s going to save a lot of extra time when your web server has to go to your database and look through comments. If it’s got 100,000 spam comments, it’s going to take a while to scan through all those to display the ones that are the right ones for a particular page or post.

Another thing that takes up a lot of space inside your WordPress database is the feature that was released in Version 2.6 and this is the post revision feature which allows you to – you can store extra versions or older versions so you can restore an older version of a post or page. And by default, it stores infinity number so if you keep revising a post or page, it’s going to store every single one of those. You can put a line of code in your WP-config file to limit that to say maybe you only want five or you want the latest two or whatever.

But another way you can optimize this is you can go in and you can delete all of these extra revisions. I believe there’s a plugin. I’ll have to look for it. Or a line of code that you can basically say if any post is older than 30 days old, delete all of the revisions. Because in general you probably don’t need those revisions. So if you have 100 posts and each post has 10 revisions, that’s an extra 1,000 lines in the database that don’t necessarily need to be there. So that’s something that you could remove as well.

You could delete unused tables. If you’ve deleted a plugin, you can go ahead and just remove the tables and get those out of there because they’re no longer needed and they just optimize your database as well. You can do it with a plugin called WP-optimize. It’s a free plugin. It’s installed on more than 500,000 WordPress installs and this plugin will allow you to remove post revisions, old meta data, draft posts, and you can bulk delete trash comments. And so this will give you the ability to go in and do all of these things without having to write a line of SQL code which is really nice. Okay.

Lastly what I want to talk about it is one of the tools that you can use to manage your database. Most web hosts will use some sort of tool. Most commonly I’ve seen is called phpMyAdmin. And this looks like a technology built in 1992. It’s very, very basic and it’s very stylistically poor. It doesn’t look very good. But it’s a way to go in and you can look at all your tables and you can make edits and changes to specific line items in your WordPress database.

Another one that I like a lot is called Sequel Pro and it is a Mac application that you can install. And you can basically connect to any database. So I have mine set up so I can connect to my live database within my live Your Website Engineer database. I can connect to any of them that are located on A2 hosting. I can do any of them that are located right on my own computer. And so with those three things, I don’t have to log into different portals to get to MySQL.

A lot of times, the phpMyAdmin is locked behind your username and password for your web hosting company so generally you have to log into your web host, go to their control panel, click on phpMyAdmin, add your credentials again. So it’s kind of a pain to get to and it’s kind of a pain to get everything set up. But Sequel Pro, the program that I use, actually works really, really well. And it just allows you to easily filter things.

You can sort database columns so you can see things that all have kind of the same – maybe you have all of your posts start with show notes or announcements like mine do. And I could sort them by that so I can see every single one and see is there any of my posts that need to be revised because they’re not using the same format that I’ve been using or whatever. It’s really kind of a neat thing. So I’ve got a link to that in the show notes as well.

But this is just kind of an overarching, general, here’s how MySQL works, here’s how databases work, and here’s how the technology works within WordPress. It’s very, very powerful and it is probably one of my favorite things about WordPress because this is where all of your data is stored. It’s all completely stored here and if you ever – if anything really happens, if your website loses data, it’s all because it’s been lost in MySQL. So we want to make sure that we’re always backing up your database because that is where all of the valuable information is stored. But it’s so powerful. You can do a lot of things.

And once you become a power user and can learn just a few tips and techniques, you can do things so much quicker right within the database than doing it through WordPress. But, again, you don’t necessarily need to know that to run a successful website. So that’s all I want to share with you today and we’ll be back next week to talk about more great WordPress things. Until then, take care. Bye-bye.