 All right, thank you, everybody. I'm excited to be here in Belgrade to talk with you today about the WordPress Options Table. So the WordPress Options Table is where WordPress keeps a lot of important data. It stores site settings, widget data, theme settings, plugin settings, and pretty much anything else that developers want to store there. Now, it's a pretty simple table. It stores a key, a value, and whether or not to load the option in memory. Now, pay attention to this presentation if you develop WordPress plugins, themes, or even maintain a WordPress website. Because even though this is a pretty simple table, left untamed, this table has the potential to slow down your website, or even worse, as I've learned the hard way. So a while back, I was working on a high-traffic news website at 10-up, and we started getting complaints about slowness. There were some really mysterious things going on. The administrators were changing site settings, and they also weren't showing up on the front end. I went into the database and tried to figure out what was going on. It looked like things were saving correctly, but for some reason, they weren't showing up. My theory was that caching was to blame, because every time I cleared the cache, things started showing up again, and yeah, things started working. But after a couple of weeks of clearing the cache every day, I figured something else must be going on, so I decided to take a closer look. So when I took a look into the options table, I found it had only over 20 megabytes of data, which is over 20 times larger than it should be. It turns out a popular WordPress plugin had synced with Google Search Console and pulled down tons of data and stored it in the options table. Now, the options table wasn't meant to store lots of data. 10-ups recommendations to keep the table at one megabyte or less, and the reason for which is that many caching plugins have a one megabyte limit by default, so if you are requesting data over this amount, it's going to serve either stale data or maybe we'll have to re-request queries, which defeats the purpose of having a cache. So next, I looked into the custom themes PHP code. What I found there was that the add option and update option functions weren't specifying whether or not to autoload, and because of that, it was relying upon WordPress's default, which is yes for these functions. Now, it may not necessarily make sense now of why you shouldn't autoload. I won't go into too much details now, but I'll come back and explain why you might not want to autoload all the options in your options table. Just keep in mind that you should only autoload options that are going to be used on every single page. And the reason for which is for the slowness is that WordPress runs this SQL query on every single page. What it's doing is it's looking for all the options where autoload is set to yes. If the table is small and it is returning a small amount of data, this table's fast and very efficient. However, if it has a lot of data, this query can take a couple of seconds to execute and it can slow down each page load. Now, the reason for which is that WordPress doesn't include an index on the autoload column. And without an index, my SQL has to look at each and every row as it's returning data. So if you have a large data set, that can take a while to do. Like I said, for now, we'll talk more about why the autoloading is something that we may not want to have for every option. But again, you might not want to set all options to autoload if it's not used on every page. So one thing I looked at as well on the options table was I noticed that there were too many rows added. Another popular WordPress plugin had added over 5,000 temporary rows of options. So what it did is it added a new row every minute during a sync. But because of a bug in the plugin, it never deleted those rows. Over time, it filled up the table and we had a huge WordPress table. Since the options table was never meant to store tons of data, my recommendations to keep the table at under 500 rows. And because you never know which options, plugins, and themes are adding, you want to regularly check the options table to see how large it is. So now that I discovered what the problem was, how did I fix it? Well, the first thing I did is I ran a SQL query which deleted the extra rows that were added from that plugin. This brought the table down to a much more reasonable size and things were able to run a little bit better. Next, I ran another SQL query which updated specific options which didn't need to autoload. These were options that weren't used on every single page and weren't necessarily needed to be loaded into memory. Next, I went into the custom themes PHP code and I specified whether or not options should autoload or not. By passing no, many options were changed to not autoload which helped performance overall. Finally, I configured the WordPress plugin that had synced with Google Search Console and turned that functionality off because it turned out that that wasn't really needed anyways. Finally, I uninstalled any plugins that had added any options to the options table that were not really needed. Since plugins can add their own options or as many as they want, these plugins weren't really necessary so it was decided to remove them from the website. Now, as I mentioned before, it's important to keep an eye on how your site's doing. One of the tools I like to use to do so is New Relic. And New Relic provides really precise reports into specific WordPress hooks queries and MySQL queries so you can pinpoint exactly what is slowing down the site. Now, using a tool like New Relic is important because you can figure out whether or not the options table is what's slowing down your site or something else deeper is going on. Another tool I like to use is WPCLI. By running a command like the one here, you can check and see how many autoload options are set in the options table. You can also check and see how many options are there overall in the table to see the overall size. Likewise, if you have access to run a SQL query on your server, you can run a SQL query like the one here, which will tell you pretty much the same information about how many options are autoloaded and how many options you have in the table overall. Now, let's go back to autoloading for a minute and I'll talk a little bit more about that. As I mentioned earlier, WordPress doesn't include an index on the autoload column so when MySQL runs a query, it has to look at every single row to determine whether or not it should return it on every page load. There's an active discussion about this going back over five years on WordPress Core where developers have been debating whether or not to include an autoload index on that table. One of the arguments against is that the table wasn't really meant to store tons of data so there really isn't any need to have it. But argument four is that since the functions default to autoloading set to yes, it might be worthwhile to include that for scalability and so that maybe this problem wouldn't occur in the future. It's a good question and the debate seems to be still open. If you do need to store lots of data on your site in the options table, you can create an index for yourself. You can run a SQL query like the one here and that will create an index on the autoload column. Now, this may not be necessary for all websites but if you do find that the SQL queries on your options table are running slow, this could help out. So to summarize the lessons I learned here, you should always make sure you monitor your options table to make sure it's less than the megabyte in size. You should always make sure you're using the functions in your themes and plugins correctly so that you're specifying whether or not to autoload. You should keep the number of rows in the options table to less than 500 total and if necessary, you can add an index on the autoload column if you need to help speed up your site. Thank you, Hvala, and I'd be happy to take any questions. Thank you, thank you so much. Questions? Again, okay, a mic here and a mic there, thank you. Yeah, I'm only curious why one wants to use the options table when there are a lot of places where you can save data on WordPress database. Yeah, it's a good question. I think that for new developers who may not know the different ways that you can save data in WordPress, the options table sometimes comes up as a viable option. I see it a lot as the place where a lot of plugins are storing data rather than creating like a custom post type that's another common way where people are storing data. I think a lot of times people are just using the functions as they think that this should be used and don't necessarily see in the documentation any warnings about the size limits there. So I think that's probably why people decide to choose the options table of other ways. Hi, I have a question about do you know any plugins that can do some of the things you mentioned for us? I mean like monitor the size of table or number of rows or such things. I haven't used any myself, but I've noticed there are a couple in the WordPress plugin repository which do these sorts of checks to see the size. I'm not sure what sort of cleanup it provides, but it does seem like there are plugins out there that would do this sort of thing. Thanks. More questions. Yes, a mic here please. When we have an option table installed, whatever who is stolen, that is a fact to transition, sorry for my pronunciation, transient. Oh, yes, it's because they, this is a transient in the WordPress function that we could do it undo, you know. Yes, so if plugin installed options table, this is related with transient? Yes, the options table is where transients are being stored as well. So those definitely add to the load, especially if you have a very large site which the site was that I was working on. If you're using transients instead of like a more of an object caching, this can definitely flip the options table. Yeah. So this is your suggestion to exclude transient or not? I would think for a larger site, you would probably want to look more into other caching options other than transients like object caching, you know, I think it's really up to you if you want to maintain the options table because you can optimize it a bit more, but that's the downside there. Okay, thank you. You're welcome. Thank you, more questions? Yeah, it is a bit hard, huh? Yeah. I don't see any hands in the air right now. We have time for one more question. I have a question. How often does this happen? It happens pretty frequently. This wasn't the only site where we had kind of weird things happening and we looked at the options table and it was totally full because there was a plug-in or some sort of a cron job or something that just kind of went bonkers and filled it up. It's very common, so you should definitely check it out if your site's performance has been lagging recently. Okay, thank you. Oh, we have a question. Hey, thanks for the nice presentation. I wanted to ask you if you're fetching data via API from remote sources and the API is kind of slow, so you might want to cache those data, then you're saying that you wouldn't use the Transience API for that if the amount of data that you want to store is quite, like, huge. Yeah, I wouldn't recommend it. So let's say you are caching Transience for every minute or so. If over time that fills up, you could fill up your database and those are sitting in there until they're invalidated if I remember correctly, so that could definitely fill up your options table. So what would you suggest as an alternative if you're saving a lot of different requests for a big amount of time, maybe? That's a good question. If you have to store it in the options table, I would make sure that it's not auto-loading on every page because that's gonna slow down every page load. Probably just keep it down to maybe a couple of different keys if possible and make sure that you're invalidating things as you can. If necessary, you could also find another way to store data such as storing in a custom post type or post metadata. There might be other more efficient ways to do so. Thank you. More questions? Come on. Yes. We have a question here, please. Thank you. So what's the, let's say, the best or the optional data that should be saved in the options table and what we should not save in options table because sometimes is needed to save to add something on the options table, but not always. So like an example I gave before where the plugin was storing data from Google Search Console in the options table, that probably wasn't the best place to store it. Even storing widgets, which is the default way, WordPress stores widgets, they store it in the options table, you can get a lot of data. If somebody, if you have a site where you have administrators adding tons of content and text to sidebars and widgets, that can fill up the options table. So I think it's a better question of like, are there better ways to store the data rather than what's appropriate? Because honestly, the widgets from what I've seen may not be the best thing to store in the options table, but WordPress does by default. But it would be difficult. Yes, they have a lot of content. But in those cases, it probably would be more beneficial to optimize the table, just to make sure that you have a large table and that the queries are very efficient. Okay, all right. Thank you, Ben. How was your first experience as a WordCam speaker? Great. Have a good time. Will you be back again? Yeah, definitely. Good. Thank you. Thanks.