 Alright, so my name is Michael Wood and today's topic is WordPress database optimization and maintenance. So this talk is going to be mostly targeted at people who understand what database maintenance is in the sense of you're probably a developer. However, we, if you're not a developer, you will have actionable things that you can actually do because there are plugins that can handle a lot of what we'll be talking about. So don't be afraid if you're not a developer, you should be able to understand this and put it into practice. There will be some words of warning as we go through for those of you who aren't developers just so that you know kind of what to expect when you get into this. Let me start out with just a quick introduction of myself. So I'm a WordPress developer at Bluehost. You can find me pretty much by Googling WP scholar and you'll find previous talks and websites and all kinds of stuff that I've done. So if you want to reach out and ask a question or follow up to this talk at some point feel free to reach out. So there's a few tools like I said there's some plugins that allow you to do a lot of the types of things that we'll be talking about. So one of those tools is a tool called query monitor. For those developers here, you're probably familiar with it for those who are not developers query monitor is kind of like a debug tool per se for WordPress. It will tell you what's going on behind the scenes. You know if things are taking a long time and are slow, like specific WordPress database queries, things like that. It will help you to see those and it will expose those types of things so obviously talking about database maintenance, a tool like query monitor which will just allow you to kind of view everything that's happening in WordPress is very helpful. So, that's one of the tools. Another tool is a plugin called WP optimize so this was a plugin created by the updraft team. So they're the ones behind updraft plus the backup plugin. So, we'll talk a little bit more about this. This is not a plugin that I normally just run on a site. I usually just use it to aid and some of these database tasks that we would do. And then this one, we probably won't, you probably won't need this. Ideally, your web host will have some sort of PHP my admin interface if you need to go and run some sort of database query. If you can run WP CLI you should be able to run database commands as well. But this is a tool that can be used if you need more database access and you don't really get that from your web host for some reason. I figured I'd throw it in but something you can play around with but we probably won't highlight in this talk. So when it comes to database optimization obviously the thing that we're really looking for is how can we speed up our site. And you know is database optimization the thing I should be doing to speed up my site or there are other things I should be doing to speed up my site. Typically when we're talking just general performance. There's a lot of things you would do probably before you get to database optimization, although one of the big things that would be helpful with database optimization is object caching, which we'll talk about here a little bit so we'll look at kind of a high level of like what are the types of things that we need to think about while we're monitoring our database and trying to figure out how it's performing. If as we go through you have questions. Feel free to post them in the chat, I do have the chat open and I'm looking at it. Do as best I can while I'm talking. So as your questions come up I'll try to answer them. And I'm sure there'll probably be a lot of questions. This is not a topic that I hear much about. So hopefully we'll get some good, good questions. So jumping into the questions to ask right so what are the types of things that you should be looking out for when you're trying to optimize or in general just do maintenance on your database. So what you want to ask yourself is how many queries are happening. And so, you know what's a normal number of queries that's going to depend on the type of site that you have right. So, in general, on average you might see WordPress make it anywhere from like 40 to 60 or even 100 queries on a page. That's not out of the ordinary. So if you have seen sites that'll make 400 queries 600 queries. And obviously, if you keep going higher. This becomes a problem. So ideally you want to reduce the number of queries as much as possible. So anything that we see happening that we can reduce is ideal. And sometimes you'll see, as you start to look at, well, where what types of things are are triggering the most queries. And I think that a particular plugin is essentially a bad actor and they're making hundreds and hundreds of queries. None of them can be cashed with object caching. And so then you have to question, you know, am I going to continue to use this plugin is there an alternative to this plugin. So a lot of times the fixes, it can be just moving away from a tool that you've been using, because you didn't realize it was not very performant. And that is if there are any slow queries. So are there any queries are taking a long time to to happen, right? Obviously that's going to slow things down. And there are specific types of plugins that do require more advanced queries and things are going to take longer. Obviously, those are the kinds of things that are core to your website. You probably don't want to just like kick a plug into the curb, because obviously it's your need a plugin like WooCommerce or something to run a commerce website. You don't, you know, if there's slow queries happening there, you probably don't have the option of eliminating that. But knowing that a query is slow and figuring out, you know, is this query cashable. Is it something that, you know, if, if we properly have object caching and stuff that we can kind of cut cut down on that. So there's a lot of a lot of little things like that we think about. Are there any duplicate queries so duplicate queries is something where if code is not using the proper WordPress approach essentially, you'll get a lot of duplicate queries. If you're seeing those kinds of things than chances are that you might be looking at some plugins that are not properly implementing the WordPress approach to using object caching or transients. And then are there any front end rights happening during normal page views that is very specific. And that is actually one of the things that if you have a high traffic website. That's probably one of the worst things that you could have on your site. So for example, there are plugins out there that want to keep track of how many visits are made to your website. And one way that people who write these plugins might do that is they say okay well, I have a counter. We started at zero and then every time somebody visits a particular page or the website in general, we'll just increment that counter. Well, that's fine. Except to do that, they have to write to the database and writes to a database are way more expensive than reading from a database. So we can easily fetch all the content metadata and all the things that we need to display stuff on the site. But the second that we have to write back to the database that becomes a slow process. And if you have high traffic sites, that's the kind of thing that creates a bottleneck that it can really cause performance issues. And as we get into more of like our storage engines and stuff that we'll talk about, it'll make a lot more sense why this is really, really bad. So, pretty much, if I see a plugin doing front end writes I usually as fast as possible just drop it. I said if there's a plugin and there's only one that it's related to your core functionality of what your site does, then you know maybe reaching out to the developer and trying to to work out you know hey can we do this a different way. It's not helping performance. So that's one thing we look at. And as I mentioned before. So are there any particular plugins or themes associated with these problem queries. Now, when we're talking about all of these things, if you enable query monitor. And you load up the front end of your site at the very top of the screen there's a little thing that will show up it's got some random numbers and stuff. If you click on that it will pop up a panel at the bottom. You can go to the database section and it will show you all the queries that are happening. You can filter by plugin and you can filter by theme. And you can look at if there's any queries that are front end writes or things like that so most of these things that you see here, you can kind of identify using that particular plugin. Let's, we're going to kind of start at the grand scale and work our way into the minutiae of database optimization and maintenance. So, speaking at the grand scale of the database as a whole. Let's look at just a few things that we can do to to optimize so first of all you just want to make sure that you have enough resources on the server to make sure that you can properly run your database without issues. So one of those things is if for some reason you're running out of disk space. It's possible that there will be corruption of the database files on that disk space. So if you don't have enough disk space on your server. That's a problem. Typically, if you're not self hosting a website. Most hosts have this all sorted out for you. However, never hurts to just verify that, you know, you're not coming up on the brink of, you know, disk space issues because either way, that's going to cause issues for your site. The other thing to look at is making sure that you have enough RAM to keep my sequel running. So if you don't have enough resources allocated to the random access memory, then what'll happen is my sequel which requires a certain amount will crash and stop running, even though the website will continue to load things it just won't be able to fetch anything the database, which obviously makes your site kind of useless. So there's kind of the two big things with server requirements just to make sure that your database is functioning correctly so. As I mentioned the next big thing is object caching so object caching, if you're not familiar is essentially every time that WordPress needs a piece of information, it will go and fetch it from the database. And so to do that, it will create a query requested, you know, I want to pick everything from the users table all the users from the users table where email equals whatever. So some kind of query like that. And some of them are way more expensive queries than others. And so the whole idea of caching is taking something that's expensive takes a lot of work, and making sure that we can keep a copy of that work as long as it is valid. And then serve up that that shorthand right so if I wanted to get the square root of 64 might take me a little while do that in my head but if I know the number and you asked me what the square root of 64 is, then I can tell you right. So being able to just kind of quick respond is essentially what object caching is doing right it says I know for this query that this is the answer so I'm not going to have to go look it up. So that's kind of what we're looking at. So, first of all, you want to find out if your web host actually offers object caching. This is not really something that you're just going to be able to install a plugin and have object caching. This is something that has to be already on the server. So, if, if your web host provides it, you should definitely enable it. If your web host does not provide it. You might want to look and reconsider your web host, assuming that the database optimization is a real problem for you, which for a lot of small sites it probably or low traffic sites it's probably not a deal killer. So, the easiest way to validate object caching is working is to use, like I said, queer monitor. And you may need to use another plugin to make sure that the object caching is actually probably properly working again, a lot of web hosts will have their own plugin to handle the actual integration with the object caching that they offer. However, a lot of web hosts also allow you to delete said plugins so if you happen to delete the plugin, then it's very likely that your object caching would not be working. So this is what it would look like. If you open up the query monitor panel on your site, whether you're on the front end or the back end doesn't really matter. And you go to this overview section. Down here, it has object cache and as you can see, it says persistent object cache plugin, not in use. However, we have this APCU object cache installed, but is not being used by WordPress and even give you a nice little link says install an APCU plugin. So, if you're seeing that you have object caching it's not in use, then that just usually means that you need a plugin to take advantage of it. And in some cases you might see oh we don't have object caching available at all, in which case, you know that's that's where you might readdress your hosting if, if the database is a problem. So typically when is the database going to be a problem if you're running a big ecommerce or an ecommerce site in general. A lot of those queries are going to be coming in to the database and it's not queries that we can really, you know, store because, you know, if one person puts a bunch of stuff on a shopping cart, we can't really store that for somebody else right. There's a lot of things with ecommerce that does require actually to be dynamic and and it does require like fresh queries to database. So, object caching can help to a degree. But, but yeah so we want to take a look at that and just make sure that we're using it so if you see that you're, you have redis available, and you have no redis plugin. This would be the redis plugin most likely you would use, unless you happen to be on Pantheon in which case they have their own redis plugin. If you happen to see that you have a memcache is available, then this is the memcache object caching plugin. Yes I am screen sharing, hopefully more than. Yeah, hopefully everybody can see. So the, the memcache plugin will will work in most cases, unless exception, you're on site ground in which case, the only way that I've found to actually get their object caching properly working is to use the site ground optimizer plugin, which does conflict with a bunch of other optimization plugins I like to use. So, typically, if there's a site ground site. Object caching is the only thing I turn on on that plugin, and then we use some other optimization plugins. All right, so one of the things that you'll want to take a look at because a lot of times you install plugin and the plugin will add some database tables, and then you decide oh I don't want this plugin it didn't do what I thought it did so you remove it. And then all of a sudden the you know once you delete it the plugin didn't clean up after itself and so there's a bunch of database tables that just are lingering in the database that really aren't even used at all. Yeah, let me let me do stop and reshare the screen see if that helps here. Just a second should be number two share. Let me make sure I pulled chat back up. Yeah, hopefully that reshare help. But yeah so you have plugins that will just not clean up after themselves, and it will leave a bunch of tables behind so this WP optimized plugin will allow you to see pretty easily if a table database table is not in use so for example you see in the screenshot here, the red items there where it says not installed, and in blue it has the name of the plugin so this is essentially the plugin is recognizing from a known set of database table names that this database table belongs to this plugin, and you don't actually have that plugin installed. So then it highlights it as this is really not in use. So that's really great. It makes it really easy to find those things that are kind of left behind. But it's not 100% perfect meaning you could go delete a database table and then realize, oh, maybe this other plugin happen to name the table the same as some other plugin was using, and you just deleted stuff you were actually using. Because like I said it just pulls from like a data source of known database tables and if there happens to be overlap, you could delete some stuff. So, the benefit of using WP optimize is that it's written by the people who created the updraft plus plugin. So you can actually install that alongside of WP optimize, and they have a checkbox that you can check before you do any database table manipulation. It will automatically back up your database as part of that process. So, highly recommend that you backup before you start deleting database tables for obvious reasons. There's a little bit more to the screenshot that's actually not showing here but for all the items that it says you know this this plugins not installed so this tables not being used over here just past this index size, there would be actually an action where you could do a remove and that clicking that button would remove that database table. So, the problem with that is, again, make sure you backup first. All right, so we've kind of looked at the database level right so now databases have tables. And so there's a bunch of tables that were press provides by default in the database. So there's users and user meta posts and post meta comments and comments meta and taxonomy tables and. And then of course we have all the other plugins that are adding things as well. So what do we do about table optimization. So, the first thing that you want to do is make sure that you're using the no DB storage engine for your database tables. And again this is a per database table. So not a whole database, you can't just change it once and have it work for everything. Each individual table, you have to set it so. There's two database storage engines so one is called my isam it's kind of the older one. And so when you are writing to the database, it will do what's called a lock right we want to make sure that if we're writing to the database that's that maybe somebody else go into the site. Isn't also triggering a query that's writing to the exact same thing at the exact same time, and then causes a conflict and then you get corrupted data and so it's a mess right so the database is smart enough to say okay, we're going to lock this down when we're writing to the database now. As you can see it says, locks the entire table when writing or updating data, which means if you have a plug in that's, let's say incrementing a value in the options table to keep track of how many people are visiting a site. The entire options table gets locked down, which means anybody in the front end admin, or I'm sorry the back end will not be able to change settings on the site. Other people going to the front end and having that same query that needs to happen will not be able to do that until the other queries done. So as you can see the more and more traffic that you start to get your, your table in the database becomes the bottleneck for serving all of the people who are coming to your site. So that is the important thing about front end writes, making sure that doesn't happen. And also that you're not using my isam, because in ODB, when it locks things down it only locks the specific row that's being written to. So that frees up the rest of the table. So in the case of a particular value in the options table being set, that still means that somebody in the back end of WordPress and it's changing some other setting, it will work. So it allows for more simultaneous operations when you're using in ODB. So that's the logic behind it. So what you can see is it to do, again, WP optimize makes it pretty simple. So what you want to do is you'll come in, and you'll look at your table list and as you can see here we have comments and comments meta. And if we look over to the right there's just a button that says convert to in ODB. And you can literally click that. And then all of a sudden, your table is converted from my isam to in ODB. It's very easy. Again, always do a backup of your database just in case something goes wrong. Because all these database operations are would normally be done by a developer, you know, and they're not as easy as a click a button, usually. It's great to have the ability to do that. But you just got to be careful and back back up your site, just in case. I think the convert in ODB is actually really safe to do. I've never had a problem with it ever. But you never know what might happen. So it's always good to get that back up. You can also use WP CLI. So if you're familiar with WP CLI, you can do WP DB CLI, and that will allow you to write my SQL commands to essentially do the same thing. So show table status will basically just kind of list all of your tables. So table status where engine equals my isam will show you all the tables that you have in the database where the engine database storage engine actually needs to be updated. And then if you actually want to fix it for each table that you find, you'll run this altar table. And the table name engine equals an ODB, and that will update it as well. So if you're wanting to do this without installing a plugin and you have easy access to WP CLI, like for example, WP engine has their little WP CLI terminal built into their hosting platform, you can actually do this kind of thing there. And then we want to look at optimizing tables. So optimizing tables. So by default, and this is an interesting thing with WP optimize by default, they don't actually optimize in ODB tables, and they have this warning that shows up. And the reason they do this is because sites that are running older versions of my SQL. It's actually kind of a risk to actually try to optimize these in ODB tables. But you can do it if you're running a more recent version of my SQL so you can check the box to optimize those tables anyway. It's going to do the most good for the my isam but obviously we're going to convert those. So being able to run this database optimization is very helpful. If you want to run that from the WP CLI, you just run WP DB optimize and it will take care of it automatically. And then if you want to take a look at the date, the table sizes to see you know is there a particular database table that's ballooning in size that we need to look at or address. You know you can use WP optimize and it shows you the size of the data in those tables, and it also of course list the number of records. So if something is just incredibly large, which a lot of times the options table can get very large. Particularly if you have a heavy traffic site. There's sessions to get stored in the options table and so if you get 3 million people visiting your site you could have 3 million rows in your database table for all the different sessions. So, and then of course there's transients and all those, depending on whether you're using object caching or not can get rid into the database. And then you know there's a bunch of different things that can that can actually slow down that options table. So usually that's that's where you're probably going to see the largest size. But you know one 1.5 megs is not that much. You know my sequel can handle quite a bit. You know, 5 million roses. You know it can handle. But obviously if you're at like 3 million things are going to get pretty slow. So you don't probably want to keep it pretty low, but if you have a big high traffic site, then expect to see that be a much larger. So removing unnecessary data can actually cut down for example on the size of some of these database tables so post revisions for example. In some cases, post revisions would just be stored indefinitely right you could have 1000 revisions on a single posts, and they just sit there. There are ways that you can configure in the WP config file. There are ways that will limit the number of post revisions per post to say 10 or 30 or whatever you think is appropriate. But obviously at some point, we need to make sure those revisions get cleaned up. So if you haven't set a specific value for how many gets, you know, how many persists, then WordPress isn't really going to like auto trash those, and you have to go clean them up yourself. There are auto drafts so every time you say I want a new post. It creates an auto draft so even if you leave and technically there's an auto draft. So sometimes we have a lot of those that just kind of sit there, and also need to be removed. So if you delete a post. And for example, maybe you don't have a specific timeframe in which the trash is set to be emptied which is again configurable from WP config. If you don't set that then trash posts could sit there again indefinitely and build up over time. And then of course spam comments and comments that have been trash, most likely they're spam anyway. Those, those can obviously build up quite a bit. And so sometimes it's a matter of well I don't use comments so let me just turn off comments and then clear out the ones that we have. Sometimes it is, you know, let's enable a fiss matter some other spam prevention plugin to cut down on the spam so we just kind of have that auto taken care of WP optimize does give you the ability to like schedule some of these types of cleanups. So if you want to say, delete all spam within, you know, over the two weeks, it would, you'd be able to do that and have it happen like once a week or whatever. So then any unapproved comments or expired transients, and this one's a little more risky to do but again you can do it with the WP optimize plugin which is to clean up orphan data. So for example, let's say you've added some data to a post and well, there's a lot of different scenarios where where data just kind of just ends up floating somewhere in a database table and the related item got deleted. So, all those kind of use cases are handled. But again, that's a back it up first kind of thing so this is the interface where you can do that in the WP optimize plugin. Let's see. So examples of transients. Yes, so transients. So, so there's a, we'll talk a little bit more to in depth about transients, how they work with object caching and all of that. But basically, if you have no object caching transients are just stored in the debt and P options table. And so the way it works is, you know, let's say I made. So I wrote a plugin and my plugin hits some other service like say Twitter, and maybe grabs a list of tweets or something like that. I can take the results and just say, hey, I want to store these as a transient. And so that would get put into the options table. And of course, you can store small things and you can store really big things. It's better to store small things. Because the bigger you get the slower things are going to get with your database so we'll talk a little bit more about the size of things in that options table as well but transients, they're just, you know, the equivalent of an object cache when there is no object it's a key value pair situation so if I say I have this thing I want to save it I want to give it this name. Next time I call it by this name give it back to me that's really all that's happening here. All right, so we've kind of looked at the database we've looked at the tables and now we're going to look at the queries themselves that are coming in to the database and how we can optimize these so this is where we get a little bit more into developer territory. Like I said that we've kind of given you developer ways and user friendly ways of doing most everything else. But usually if you're talking queries, you either can suggest to a developer that they improve the performance of something if you happen to see something weird, you know, happening with a particular query. I don't know about what's happening to ask for it. But that's what I'm saying most of this section is going to be more developer focused. So, when you make a query in WordPress. You can just make the query. If you don't use the WordPress caching functions for the WordPress transient functions. What's going to happen is, every time it's just going to hit the database and fetch or run that query whether it's a right or a fetch or whatever. I'm going to make that query, and it's going to be an efficient right because every time and it's going to always make that query so if you know it's possible that you could cash it. That would be the way to go. WordPress provides WP underscore cash functions, WP cash get WP cash set WP cash delete things like that, that you can use when you're writing your code to make sure that all of the important things that you want to store that, you know, extra work that you did. You can just fetch it by name next time that that's the way you want to go right so here we can see some code. It says, you know the cash key is my items, which is just obviously you would come up with a better name for the specific thing that you're trying to store. And we're basically saying well if, if we do a WP cash get for that cash key and it returns nothing, then we need to do the query anyway. So, but otherwise it would skip this condition and just return the query which is set up here. And this is probably not the best way to write the code from a best practice standpoint but it does fit on the slide much better so just give me that. Okay, so you make the query and you say okay, I want to do a new WP query and I'm going to fetch some stuff and it stores it all in the query. So I'm just going to store that whole query using WP cash set and so it's going to store it at the whole query as my underscore items. And then it is going to. So you can do some grouping that's what this empty string here is, and then you can set the amount of time before which that expires right so day and seconds would be one full day. You could do day and seconds times 10 if you want it for 10 days, or, you know, minute and seconds there's a bunch of constants that you can use WordPress to get a specific timeframe. So this looks great right so you like okay, we're using the cash functions which means if object caching is enabled, then this would be cached, which is true. But there's one fatal flaw with this particular bit of code in this slide. And if anybody wants to lead your guests in the chat feel free. Otherwise, I'll go ahead and reveal. Basically, there is one major exception. And this is actually a horrible practice, the way that it's done on this slide, because you should never cash in WP query instance so when we're talking about the size of things in the database. Basically the size of a WP query instance is pretty enormous. And top it all off every WP query instance stores a copy of your database username and password, which is not something you want replicated all over your database because if there's some code that can read a particular value. Then you're basically giving your database access to to them right. But like I said, the query is huge. So if you are for example trying to fetch, you know, I don't know, 100 users or something so you have all the user data, and it's stored in the query. And you're storing the whole query. You know that's that's pretty big and it has sensitive data. So it's better to get the IDs of the users and just store a comma separated list of IDs in the database than it would be to to store all this other stuff right so that's what we want to be careful about when we're thinking about how we're putting things into a database. So, as I mentioned we have these WP cash functions and we have these transient functions. And so we're going to kind of take a look at the similarities and the differences between the two. So if you use the WP cash set function, it will use object caching, if it is present. However, if there is no object caching it will fall back to what is called a runtime cache, meaning if WordPress is loading up and it needs to make a query. It will make the query in the database, and it will, it will essentially remember that for this one particular page load. So if that query is made again. It won't make the query again it will just fetch it from this runtime cache. So it prevents duplicate queries on a single page load. But, you know it only stores it for that one page load so if you have a lot of traffic, you're just making the queries, all the time if you don't have object caching. It does that the ability to group entries, the whole idea behind grouping entries is, you're able to say okay all of these things are together and so that means you can clear the group of entries as a whole. Right so you can flush essentially a subset of the cache. Assuming that you've grouped them in a particular way. So for example, I think WordPress does this with post meta. It might group them by the ID of the post and so it makes it easier to clear everything for a particular post. So having a fall back, or I should say the fall back is not persistent. So if you fall back to that runtime cache, it's not going to persist past a single page load, basically what that means. But it does, as I said, prevent duplicate queries. So what does that look like on the set transient side. Oh, one more thing yeah you can you can also set the expiration. So on the set transient side, it will use object caching, if it is present so that's the same as WP cash set. However it falls back to database storage so if you set transient it will actually store your transients into the WP options table. So this can be good or bad right so if. Well, with any object cash there's usually some data storage limit. And it could be as little as like two megabytes. So depending on the amount of caching that's happening if you're caching tons of stuff, it could just be kicking things out of the cash normally. Well, if you're falling back to the database it's not kicking things out it's just constantly throwing new things into the database so if you're what I would call over caching. Then you would have a problem of the database. If object caching isn't actually present using set transient database starting to balloon in size, which again, that also becomes an issue so things to think about. You can't group entries with set transient, which means that you have to flush all the transients, or you just delete one by name at a time. So you can't do a subset. The fallback is persistent. So if there's no object caching it will, you know, continue to use that transient as long as it's valid. You can do duplicate queries not just on the specific request but across multiple page loads. And then of course you can set the expiration for those as well so kind of a comparing contrast on those two. So there may be cases where you might want to use one of the other you're just going to have to weigh the pros and cons. One of the other things about queries when you're making a query to the database, the number of joins will very directly impact the essentially the overhead or the performance of the query. So as you can see, here we have a meta query as a relation is doing two comparisons for meta and then somewhere below that it's doing some sort of taxonomy query and then it's comparing a few things there. So the more of those kinds of things that go into a single query, the slower that queries going to get. I think it's even exponential potentially with with joins so important to avoid unnecessary joins obviously there are certain things that would have to be there to make things function right. But if you get a little crazy with it, you're just hurting database performance so good to make sure that you kind of check yourself when you're using those types of queries like meta queries taxonomy queries and take queries. Let's see we have a quick question here says maybe something, nothing to do with database Google tag manager Google analytics slows down the site. Is there anything you can do. Yeah, definitely not database related so we're not going to obviously delve into that right now, but Google tag manager and Google analytics can slow down the site and so just really cool side tip I would recommend looking at. I always say it wrong but cloud flare. It's not as a razz it's something very similar, but they actually can load up a lot of the tags like Google tag manager does Google analytics and things like that. And basically what that means is it takes away a lot of that. It can load things way more perform than if you just use those directly but like I said, not database related. But a little side tip there. All right so we're getting kind of into the last 10 minutes here there's a few kind of little advanced things just want to get your brains thinking about, because if you kind of do all these things and you're still having some issues. Some other things you might want to look at. And then we'll kind of there's resources at the end and of course if anybody has questions will answer but so like I said you know if you for every person who logs into your site right so like if you just have a bunch of people visiting your site. You know like WordPress session associated with that and WordPress doesn't use sessions in the sense that if you think about PHP sessions and things like that that's not really the same thing it's a session stored in the database. So if you have an e-commerce site and you have a bunch of people logging in to view their account and stuff like that. Storage will balloon. And so you may need to come in and clean that out now deleting a session is not a huge deal all that means is that the user will be logged out and I've logged back in. So, you know if you have 3 million session records in your database but only like 150 people are actually logging in and on a given day, then you know it makes sense to to clear that out. So, this is a query that you can run to see how many of these sessions you might be getting of course you can wrap this little star with account at Perens to see the exact count. But you can also just do a delete from WP options where option name equal is like WP session that will clear out all those sessions. And there's probably some plugins that will actually do that clearing as well which may be a more reliable way to do it. Another thing to think about is the options table has a bunch of things that will auto right so anytime you love WordPress whether you're on the front end of the back end or wherever, as long as WordPress is loading. It will always run a query to load all of the options in the options table where auto load is set to yes. The idea is to fetch everything in a single query, because it would be more expensive to do it in individual queries, get everything in a single query that needs to be available all the time. And then make that accessible to the plugins and things that need it right. So that's the idea behind this auto load. It used to be that WordPress didn't put an index on the auto load column and it was performance thing and all your web posts would do that. But now you don't need to do that WordPress takes care of that for you but the issue here is one you may have a lot of things that are auto loading that don't need to be. And you could also have a lot of things auto loading that are just humongous. And you wouldn't really know that's what's going down your site unless obviously you took a look to see what's going on so this could be something that's happening. So this is a query that you can run to check the overall size of the data being auto loaded so you can say okay well, you know, my threshold is five megabytes 10 megabytes, you know, 1000 kilobytes whatever you want. And so you can you can run the query kind of figure out the overall size of what's being auto loaded. And then you can kind of take that a step further. And you can check to see, let's say we take the top 10 things and we want to see which ones are the biggest right so it might be that you have one value that's just huge and everything else is kind of small. So this will kind of give you an idea of size for a particular thing and you can see also as part of this what the plugin by the option name you can usually determine what the plugin name is that's associated with that particular option. So again you can evaluate do I want to use the plugin. Do I want to kind of like run a query to not auto load this particular value to change the value of the database to auto load. No, essentially. So there's a few things like that. And this query just is a way for you to easily find all of the auto loaded data for a plugin. Now, let's take this jetpack example right so jetpack does load a decent amount of stuff in the options table and auto loads it. Obviously, you have the option to not use jetpack. But in some cases, you've already stopped using jetpack but all of these options are still in the database. So that's a use case where it makes sense to come in and say, what are the things that are auto loading and what are these things are not used anymore it's kind of like the tables that were deleted or that are no longer used because the plugins not there. So you could do a similar cleanup here. There's no plugin to date that I'm aware of that handles this kind of thing. So it definitely is a developer task. But it is something that is worth looking into especially if you have a lot of things being auto loaded, especially if they're very large things being auto loaded. So the resources screen here will post on the meetup page a link to the slides. After the talk. And, but yeah so these are the things that we're using right so we have a link for query monitor. So I recommend you just kind of go to the query section and query monitor and look and kind of play around with that. We've optimized so we've already shown you a whole bunch of features of things that you can on just two screens and that plug in you can check up and get a pretty good idea of what's going on with your database. The PHP my admin plugin. Again, something I don't really recommend unless you absolutely need some other way of getting into your database. So that is WordPress admin or WordPress access, you can actually run a tool like this. And then, you know the redis and memcache plugins. If you need to utilize object caching that exists at the host. But again, you know if you have a particular host also should just look to see if their custom plugin would run for that. We've included a couple tutorials so this one is how to use query monitor I know I didn't talk too much about how to use it. But this will walk you through with screenshots and everything how to how to use that. And then a little tutorial on how to properly cache query results. So if you use those WPC like commands that we highlighted, and then we have kind of some advanced reading here, which is. So if you liked the last three slides that I went over the advanced section is probably for you. I've got a lot of a lot of details on how to kind of break apart queries and figure out, you know, what about this kind of change to make it more performant and if you're creating custom tables for your own plugin. You know how should I set the indexes and you know how do I go about doing this kind of options table cleanup so those are the resources. We're about at time but if anybody has questions, go ahead and post them in the chat. And again if you do have questions later you can always go to WPColor.com I've got a form there you can submit contact form for questions, and you can find me WPColor on Twitter and hit me up there as well so. I appreciate everybody coming up.