January 30, 2014

For episode 12 we decided to tackle the subject of using custom database tables in plugins. Should you use them? Are the postmeta, usermeta, or options tables better? Worse? There are many, many aspects to consider when it comes to structure your plugin’s data storage. We both have experience with using all of the above methods and use our experience to weigh in on the benefits and disadvantages to using each of them.

This episode was sponsored by WP Ninjas, the creators of the Ninja Forms plugin, a great plugin for creating powerful forms in WordPress with a simple drag-and-drop interface.

Show Notes:

Photo Credit: Jonas Merian

Transcript

BRAD: Welcome to Apply Filters, the podcast all about WordPress development. I’m your host, Brad Touesnard, and joining me, as usual, is my cohost Pippin Williamson.

PIPPIN: Hi, everyone.

BRAD: Today we’re going to be talking about custom database tables in WordPress.

PIPPIN: This episode, as well as previous episodes, were once again sponsored by WP Ninjas, the creators of Ninja Forms, which is a great, free plugin for WordPress that allows you to create contact forms, surveys, et cetera.

A little piece of news from them: they recently released a really cool new extension that’s called Table Editor. So when submitting your forms, if you want the users who are submitting to fill in tabular data, rows and columns of a whole bunch of different data that you may need, whether it’s T-shirt sizes or user info, anything else that you might need, this extension allows you to do that very easily. So you can check it out at NinjaForms.com. Thank you once again for their sponsorship.

BRAD: That sounds really cool. I am going to check that out.

PIPPIN: Yeah, it’s pretty sweet. It gives you like a spreadsheet view in the form submission.

BRAD: Yeah. Cool! So should we talk about some custom database tables?

PIPPIN: Yeah, but let’s actually first give a quick mention about what we’ve been working on recently.

BRAD: Sure.

PIPPIN: I think we kind of skipped over that last time, and there are a few things that might be fun mentioning.

BRAD: Sure.

PIPPIN: What have you been working on?

BRAD: Yeah, so my renewals are coming up soon. My customers are going to be renewing their licenses for the first time.

PIPPIN: This is for WP Migrate DB Pro?

BRAD: That’s right, yeah. And so I just need to do some testing around that and kind of make sure everything is in order, so I’ve been working on that. What have you been working on?

PIPPIN: Cool! Outside of Easy Digital Downloads, which, for anybody who is listening is probably familiar, is kind of my main project. My other primary project that I work on is a membership plugin called Restrict Content Pro, and I’ve been working on getting version 2.0 written up.

For the last six or seven months, the plugin has been not really stagnated, but I’ve only been doing maintenance releases on it, fixing little bugs here and there, occasional minor features. But now I’m actively working on version 2.0, which I’m trying to put a whole bunch of the new features in that have been requested for months, years by a whole bunch of different users.

BRAD: Cool!

PIPPIN: And so I’ve got some of it finished up.

BRAD: Is Chris Coyier using that for his site?

PIPPIN: Yeah. It’s the plugin that runs the lodge at css-tricks.com.

BRAD: That’s cool.

PIPPIN: Which is fun. Yeah, I’ve gotten to work with Chris a couple of times on bugs, feature requests, things like that. His is a great site as an example of like what the plugin is good for.

BRAD: Nice.

PIPPIN: One of the things I’ve been trying to put into it, just kind of fun, is the ability to automatically restrict content based on like the category that it’s in. Right now it works by any time you have a post or a page or custom fields type, you can use a combination of short codes and/or some options out of the Meta Box to restrict that individual post or page.

BRAD: I see, yeah.

PIPPIN: But a whole bunch of people have wanted to be able to do it automatically based upon the category that something is in. So if you put it in, say, the members category, it’s automatically restricted, and so that’s the feature that I’m working on building right now.

BRAD: Cool!

PIPPIN: Yeah.

BRAD: And so that integrates with EDD.

PIPPIN: It does a little bit of integration with EDD. I mean, it’s definitely a separate system. The closest integration that we have is, we have one called member discounts for EDD that allows you to give discounts to paid subscribers inside of RCP.

BRAD: Okay. Cool.

PIPPIN: Yeah.

BRAD: Nice.

PIPPIN: So why don’t we go ahead and jump into some discussion custom tables. Take us away with that, Brad.

BRAD: Yeah, sure. So I guess the question is, you know, when should you store data in the meta table, so post meta or user meta, or all these meta tables, which are just really key value stores. Right?

PIPPIN: Mm-hmm.

BRAD: When should you use those, and when should you not use those? When should you create a custom table that has your own field types and, you know, completely custom table?

PIPPIN: Sure.

BRAD: I actually wrote a blog post about this a while ago because I ran into issues just using custom fields with a WordPress site I was working on, and I decided to try custom tables, and it worked out really well. So I kind of just shared that with people on my blog.

PIPPIN: What was the premise of that?

BRAD: Yeah, it was —

PIPPIN: You want to store like post meta type stuff inside of a custom table?

BRAD: Sorry?

PIPPIN: Were you trying to store like custom fields as in post meta, but stored in a custom table instead?

BRAD: Yeah, so I was working on WP App Store, so I was basically doing ecommerce kind of stuff, and so I was having a hard time storing an order, like the order information, like the order details as post meta because I found the queries were getting really complicated.

PIPPIN: Sure.

BRAD: They were getting, like especially when you went to run a report, there was just, you know, like five joins or something just to get a few.

PIPPIN: Yeah. I’m actually looking at a query that you have in that blog post, and it’s just obscene.

BRAD: Yeah. It’s nuts, right? There’s like, how many, six joins there, I think. And then, you know, if you just create a custom table, called like order post meta or something with where each column is a field, then you reduce that to one join, right?

PIPPIN: Sure.

BRAD: All you have to do is join the post table with this custom table.

PIPPIN: So you had a very specific set of meta fields, in this case related to an order, that you wanted to store, and you wanted to relate to an item in the post table, correct?

BRAD: Exactly, yeah.

PIPPIN: Yeah, and I think that makes sense. I think that’s a really good example of something where a custom field does make, I mean, a custom table makes sense. Another example that I’ve used, so like Restrict Content Pro, the plugin I was just talking about uses several custom tables. It uses one for discounts. It uses one for subscription levels. And it uses one for payments. And the payments is the big one because, like what you discovered, if you want to run a report, and you have a whole bunch of payment data that is stored in post meta, since each piece of the meta data is stored in its own row, you have to do a lot more work to get all of that info, as opposed to with a custom table all of it is one row.

BRAD: Exactly, yeah.

PIPPIN: And so if you can very easily just say, show me the post meta for this record, you’re done because it’s one row. Like in RCP, the payments table that I have just has all of the different fields that you need: the user ID associated, the subscription ID, the amount, the status, the transaction ID, et cetera. And because all of that info is stored in one row, it’s very easy to run a report or query. For example, I can very easily tell you exactly how much was earned over the last month or in the last six days all the time because you can do a very simple sum function.

BRAD: Right. And I mean, you can still usually use the sum function or a count or whatever using post meta, but it’s just really slow.

PIPPIN: Yeah, and it’s going to get slower and slower the more records you create.

BRAD: Yeah, exactly. I mean, so the meta value column in the meta tables are of type long text, which in SQL is not a very performant type of column.

PIPPIN: Well, it’s not something that you can query on very well.

BRAD: No, exactly. And I’m not even sure if you can index a long text column. It might not even be possible.

PIPPIN: I’m not sure on that.

BRAD: But why would you either? In a table like that, in a key value store, there’s really no point in indexing the value column, right, because the values are all different or different sets of data, so it doesn’t really make a whole lot of sense to index that. Yeah, so I just think that you can gain a lot by having a custom table.

PIPPIN: Absolutely. In EDD, we actually use post meta for all of the payment records, and it’s something that we’ve managed to overcome some of the challenges pretty well, and so we can still run reports pretty easily that are very performant. But it was definitely challenging. And if I were to do it again, I would use a custom table. Knowing what I do now in terms of like —

BRAD: Right. Do you use any custom tables?

PIPPIN: Huh?

BRAD: Do you have any custom tables in EDD at the moment?

PIPPIN: Not in EDD, no.

BRAD: No? Okay.

PIPPIN: Uh-uh.

BRAD: Interesting.

PIPPIN: Well, so what’s been — I mean it’s been kind of a fun challenge to overcome, but we’ve realized pretty quickly on that storing everything in post meta, while it’s super easy, presents some challenges and performance issues as well. So we had to work pretty hard to figure out how to get around those, and I think we achieved that really well because everything is very performant, and we can run all of those advanced reports like we want.

But if I was to do it again, I would not use post meta for several reasons. One, because it’s easier to write an API that makes your data in your custom tables easily accessible for other users than it is to use the existing API in WordPress like get post meta, add post meta, et cetera, and make that performant with tons and tons of data.

BRAD: Mm-hmm.

PIPPIN: So I would not use post meta again, but we’re also in a position where we can’t really change it. I mean we could, but it’d be very challenging to change it now.

BRAD: Right. I think the post meta tables, I think they were mainly designed originally for things to display kind of that’s related to the post.

PIPPIN: I think you’re absolutely right. Well, if you look back to, I want to say Kubric did it, one of the old default themes, as well as some other themes. They display post meta automatically.

BRAD: Oh, really?

PIPPIN: It was very much meant to add custom data that you’re going to display on a post.

BRAD: Right.

PIPPIN: And that’s where, like, if you put an underscore at the beginning of your post key, your meta key, it sets it as private, so that doesn’t automatically get displayed.

BRAD: Right, right, and you can’t edit it either from the custom tables.

PIPPIN: Right. It was definitely designed for displaying data, not necessarily storing tons and tons of data in.

BRAD: Right. And it’s just kind of evolved because, I mean, you can use the WP_Query object now to query custom fields or post meta, we’re calling it. Yeah, so I guess it’s just evolved to this kind of beast that people can use to attach any kind of data to a post.

PIPPIN: Yeah, absolutely. Well, and I think people realize very quickly some of the advantages of using, say, post meta or user meta instead of some custom tables. A couple things: Number one is, you don’t pollute your database with additional tables that may not be needed when you stop using a plugin. Assuming, of course, you don’t have an uninstall script.

But, two, it’s just really easy. I mean, any developer who understands how to execute a function can suddenly store data very easily. And I don’t think that necessarily wrong or bad or anything. Really, I think that’s awesome. I think that shows that WordPress is very easy to — it accommodates people very easily. But it also means that people will start trying to do things with it that it’s not really designed for.

BRAD: Right. Yeah. What was your first point again?

PIPPIN: Oh, that using post meta or user meta in a plugin can be really nice for developers, but also for users because it doesn’t “pollute” your database with, say, additional custom tables.

BRAD: Right. I guess I’m going to debate you on this.

PIPPIN: Sure.

BRAD: I think my argument against that would be: but then the data that your plugins leave behind right now is just stored in your options table and harder.

PIPPIN: Right. It’s kind of like hidden there as opposed to being visible.

BRAD: Yeah, exactly, so it’s worse. If you could just drop —

PIPPIN: Yeah.

BRAD: — the tables for the old plugin —

PIPPIN: I think they definitely both have their advantages.

BRAD: Yeah, yeah.

PIPPIN: Or disadvantages. I mean, I’ve used both, and I think there are great reasons for both methods as well.

BRAD: Right. I’ve heard the argument that some database users won’t have create, the create privilege to create new custom tables, but I don’t —

PIPPIN: You know, I’ve seen that as well, but I think it’s kind of bullshit.

BRAD: Totally.

PIPPIN: Let’s say in a plugin, and you don’t have the create privilege, well, you’re probably just doing it wrong. I mean, I have never seen somebody use the WP DB class and run into that problem.

BRAD: Yeah. Well, plus, you wouldn’t be able to install WordPress itself if you didn’t have that privilege.

PIPPIN: Yeah.

BRAD: It’s possible, you know.

PIPPIN: I mean, I’m sure it’s happened.

BRAD: Yeah, it’s possible.

PIPPIN: Yeah, I mean, I’m sure it’s happened, but it’s got to be a stupid host issue or crazy security lockdowns.

BRAD: Yeah. Well, I guess it’s probably a good idea, security wise, to shut down that privilege if you don’t really need it.

PIPPIN: Well, right, for certain users. But inside of your plugin, you should be running as a WordPress user anyway.

BRAD: Oh, no, no, sorry.

PIPPIN: Because if you’re not —

BRAD: Sorry, I’m talking about —

PIPPIN: Maybe I misunderstood what you were talking about.

BRAD: Yeah, you totally did.

PIPPIN: Okay.

BRAD: I’m talking about the database user itself, so the MySQL user having —

PIPPIN: Right.

BRAD: — create table privilege on the database.

PIPPIN: Oh, the create table privilege, not the ability to insert data into a table.

BRAD: Exactly. Yeah, yeah, so that’s the argument I’ve heard is that —

PIPPIN: Got it.

BRAD: — some environments —

PIPPIN: So after WordPress was installed, that privilege was revoked.

BRAD: Exactly, yeah.

PIPPIN: Yeah, I can see that happening. And if it does, then your plugin would fail.

BRAD: Yeah, it would.

PIPPIN: I used to — I don’t know if I ran into the create privilege problem, but I have run into problems where my custom table wouldn’t get created. And it has been very problematic for a couple of people who, for whatever reason, we couldn’t make the table get created, and it might have been because of that. And it does cause some headaches for sure.

BRAD: Mm-hmm.

PIPPIN: And it was — I think that when I was running into those challenges figuring out why my custom tables weren’t getting created was one of the times when I started considering using the options table and post meta because, like, oh, this is so much easier.

BRAD: Yes.

PIPPIN: So much more reliable.

BRAD: Yeah. Yeah, exactly.

PIPPIN: I think one thing that I would definitely like to, at least in my opinion, when it comes to custom tables, post meta and options and user meta are fantastic if you’re not storing a lot of data. I would never create a custom data to store ten rows or five rows or whatever it is. Really, for me, I would only start creating custom tables if I had the potential of storing hundreds or thousands or hundreds of thousands of rows.

BRAD: Okay. So for me though, I think the rule is for me is if it’s data that needs to be queried a lot —

PIPPIN: Sure.

BRAD: — then I’m going to put it in a custom table. I don’t even care if it’s one frickin’ column, right?

PIPPIN: No, no, I would totally agree. I don’t mean you need to have a hundred — I don’t mean that you need to have lots and lots of columns. One column is great if you need to query on it. But the point is that if you have — let’s say that you will never have more than ten rows for whatever data you’re storing. There’s no reason to put that into a custom table because, if you only have ten rows, you put it into one option and you just loop through the options inside of that option because you only have ten sets of data.

BRAD: Oh, I see what you mean. Yeah, yeah.

PIPPIN: I mean your data — at that point, your data is so small that there’s no reason for it.

BRAD: Yeah, absolutely. Yeah. A good example of a custom table that is very performant is the Post 2 Posts plugin.

PIPPIN: Yes.

BRAD: They have a custom table that allows you to create links between two posts and that custom table just has two IDs in it, you know, two post guidance.

PIPPIN: Yeah. Isn’t it the ID of the post and then the ID of the connection or something?

BRAD: Well, yeah, it has a connection ID, I guess, so three IDs, I guess it has in the table.

PIPPIN: Sure.

BRAD: So that the two posts that are related and then it’s got like a primary key that’s an auto increment column. Yeah, I mean, that’s probably a good example of a custom table that is, you know, a great use case. Right?

PIPPIN: Definitely.

BRAD: Because it performs very well and would not perform well at all if it was in post meta.

PIPPIN: No, not at all. Let’s talk about a couple things related to custom tables that I think are important to consider. One of the advantages of post meta, user meta, options, et cetera, is that it’s really easy to migrate between sites because it’s automatically included in like the WordPress export and import or any other plugin that may do a database dump of any kind.

I mean, if you’re using a plugin like WP Migrate DB Pro by some guy named Brad, then you’re already covered because it supports custom tables. But one of the nice things about post meta and options is that you don’t have to worry about that. It’s just going to migrate anyway. So that’s something to consider for sure. If you need to make sure that your data is very portable, sometimes — I’m not saying it’s a reason to avoid custom tables, but it’s something to be aware of.

BRAD: Mm-hmm, yeah.

PIPPIN: And then another is having an API for your table.

BRAD: Yeah.

PIPPIN: Having the simple functions that allow you to insert, update, remove, and query data from your tables is very, very important because, especially if you’re writing a plugin that might be extended by other people, definitely want to do that.

BRAD: Yeah.

PIPPIN: So like in RCP, in Restrict Content Pro, for example, I wrote a couple of classes that are like my database interaction classes for payments and discount codes that allow you to query payments, have all your options for dates, for users, for subscription, et cetera. And it just provides that simple API that makes things very useful.

BRAD: Right, right. I feel like some developers feel like they need to kind of do things the WordPress way and kind of fit things into the current framework that WordPress kind of has laid out there.

PIPPIN: Custom tables are a sin.

BRAD: Yeah, pretty much. I think some people —

PIPPIN: There’s a lot of that mentality.

BRAD: There is. And I’m here to say that it’s BS.

PIPPIN: I used to think custom tables were wrong. And some of the — this kind of happened for me because I first learned about making custom tables, and I would like this is the coolest, most powerful thing I’ve ever done. This is so awesome! And so like I created a whole bunch of custom tables and some plugins for really stupid reasons. And that bit me later.

So at first I was like, custom tables are awesome! And then I started reading about, oh, well, maybe I shouldn’t be using custom tables. Oh, look; I can dump all this data in the options table. Okay, custom tables are wrong. I’m never going to touch them again.

And then suddenly I started to get into a case where I needed to store a whole bunch of data and be able to query a well and have my own database structure. I’m like, oh; custom tables are actually okay, and there are good reasons for them.

BRAD: Yes.

PIPPIN: I think it’s very easy to fall into that trap that custom tables are bad.

BRAD: Yes. I don’t know. That’s the kind of sense that I’ve gotten. I mean there are probably lots of examples out there where people are using custom table that they shouldn’t be as well.

PIPPIN: Sure.

BRAD: It’s definitely — I think there’s a balance to be had there. I think you need to really evaluate why you’re using a custom table.

PIPPIN: Right.

BRAD: And have good reasons for doing that.

PIPPIN: I think one of the number one reasons to use a custom table, and I think this is what you said earlier is that if you need to query the data, put it in a table.

BRAD: Yeah, pretty much.

PIPPIN: If the data does not natively fit into the post table with the same kind of structure as the post table, then it should probably go into a custom table. Here’s an example: I used to use a custom table — sorry. No. I used to store something in the WP post table as a custom post type. And literally the only thing I was using was the post title.

BRAD: Mm-hmm.

PIPPIN: So the post table has what, like 10, 12 columns in it?

BRAD: Yes.

PIPPIN: And I was using the post title. That’s the only thing I was using.

BRAD: Yeah.

PIPPIN: Well, I guess technically I had post ID and then any columns that had defaults that were filled in like post date. But my data was the post title only, and that was just silly.

BRAD: Yeah. So you had a custom post-type set up for that, I’m guessing, and you were just using the post title.

PIPPIN: Yeah, only using the post title. To me that’s really silly because suddenly you have all of that extra database space that’s just sitting there doing nothing.

BRAD: Yeah, and you’re making your post table slower by adding, you know — I mean, especially if you’re going to add a lot of those records to the post table.

PIPPIN: Yeah.

BRAD: You could be making the query slower from then on. Yeah, I’m not going to get into why. There are reasons for that. It has to do with indexing and stuff.

PIPPIN: Yeah, yeah, sure.

BRAD: But we won’t get into that.

PIPPIN: Why don’t we — the same kind of subject, let’s jump in to talk about session data and other temporary data and the ways that we can store that.

BRAD: Sure, yeah. It’s interesting because right now the WP_Options table is just getting kind of all of that stuff just gets put into the WP_Options table. And I feel like that’s probably not a good thing if we’re talking about keeping the WP_Options table lean and performant. I feel like temporary data would be better off in its own table separate from the WP_Options table.

PIPPIN: Right, so why don’t we backtrack just for a second? What we’re talking about here is the idea of taking session data, so let’s talk about ecommerce because this is, I think, the easiest example that makes sense.

BRAD: Yeah.

PIPPIN: When we’re talking about ecommerce, we have cart meta, so a customer comes to the site.

BRAD: Let’s talk about EDD specifically.

PIPPIN: Sure, sure, we can talk about it because this is exactly what EDD does.

BRAD: Yeah.

PIPPIN: Somebody comes to the site and adds something to their cart. They now have a session created for their user for that instance on the site where that data is stored so that we can persist that data across page loads.

BRAD: Right.

PIPPIN: When you go from page A to page B, your cart data is still there.

BRAD: You’ve got a cookie.

PIPPIN: We use a system called —

What was that?

BRAD: You’ve got a cookie set. Is that how it works? Sorry.

PIPPIN: We have two different things that are set. Number one, there’s a cookie set that contains an ID, which is basically their session ID. Then, two, we have a value added to the options table that contains all of their session data. And so that session data may be product IDs, price options, error messages, arbitrary fees added to the cart, anything that you might store in, if you think of what you might use a PHP session for, very similar kind of data.

BRAD: Right.

PIPPIN: Basically arrays of data. That’s all stored into an option in the options table, so one row, kind of like a transient, but not quite. It’s a little bit different.

BRAD: Do they expire?

PIPPIN: And so that’s all done through something that Eric Mann wrote called WP_Session, which is the idea of taking PHP sessions and bringing them into WordPress and supporting them across all platforms because, as anybody who’s tried to use PHP sessions know, they’re not supported on al hosts for some good reasons. But anyway, so we take this temporary cart data or the session data, and we store it in options. And then it gets purged over time, so there is a cron function that runs to go purge this data.

And it works really well, but it also has some disadvantages. If your site has a lot of traffic, it’s possible that your options table may have thousands of rows. Now this session data, it’s all set to auto load of no, so it doesn’t slow down overall performance of the site itself for like page loads, but it can slow down the queries to the options table.

BRAD: Actually, it’s probably worse for inserting, so every time you insert a record, it has to recalculate the index of the table, so the primary key index, and actually, probably, the meta key index as well.

PIPPIN: That’s true.

BRAD: So every time you do an insert is when you’re really — that’s like when your performance takes a hit.

PIPPIN: Right.

BRAD: So having all this temporary data in there, like going in and out and in and out all the time.

PIPPIN: Right. Now that being said, it’s not necessarily going to change a lot whether you’re doing that in a custom table or in the options table in terms of what has to be re-indexed because basically it’s a database right.

BRAD: Yeah. Yeah, pretty much. I mean, I guess the difference is that it kind of keeps the temporary stuff separate from.

PIPPIN: It keeps it in one place. Yeah, it keeps it separated.

BRAD: Yeah. Keeps it separate, and then any time the WP_Options table, you know, anything is inserted into that, it would be faster.

PIPPIN: Sure.

BRAD: That’s the only difference really.

PIPPIN: Yeah. And this is the same system that we use it in EDD. WooCommerce uses a very similar system. Theirs is slightly different. The one that I use in EDD is pretty much WP_Session out of the box, exactly as Eric wrote it.

BRAD: Okay.

PIPPIN: And then WooCommerce uses a slightly modified version of it. I think they technically wrote their own, but it does the exact same thing.

BRAD: Right.

PIPPIN: It just looks a little different.

BRAD: I had a little discussion with Eric actually about WP_Session, and I was kind of asking what he thought about having a custom table for the session data and stuff, and he actually agreed. He thinks if it ever gets —

PIPPIN: I’d like to see a session that has its own table.

BRAD: Yeah, if it ever gets rolled into Core, he said that he would like to see it having its own, like a new Core table.

PIPPIN: It’s something that I’ve considered it for EDD, creating our own session table. I know WP ecommerce uses their own.

BRAD: Okay.

PIPPIN: I think they do. I could be wrong. It’s been a little while since I’ve looked at theirs, but they have also a very similar system where they use a cookie to identify the session, and then they store the actual session data inside of the database.

BRAD: Oh, yeah. Hmm. Did you know that WooCommerce has custom tables?

PIPPIN: Yes.

BRAD: Yeah.

PIPPIN: They use custom tables for orders and, I think, customers as well.

BRAD: Yeah. It’s funny. They have a custom table for order items and a second table that’s a key value store for like order item meta.

PIPPIN: I’ve seen that. I’m not familiar enough with their database structure to know the reasons for that. I would assume it’s basically you have an order ID and then here’s the product IDs that were purchased in that, and it allows you to compare them easily. It would be my guess.

BRAD: Yeah. I can’t really guess. I know that they would need a —

PIPPIN: Yeah, I have not used them.

BRAD: I think they need the key value store because it’s flexible, right, so that people can extend it easily. I mean that’s one of the greatest reasons that WordPress Core has that.

PIPPIN: Sure.

BRAD: And that’s because it’s so easily extended and used, right?

PIPPIN: One of the issues that we did run into with session data stored in the WP_Options table is that you have to be very careful with it because it turns out, if you’re familiar with how the options API works, options API has something called auto load. And so any option that is stored in the database with a value of auto load, so there’s an auto load column, and then that value is either yes or no, anything that auto load is set to yes is automatically loaded into memory or via cache when WordPress is loaded, which means on every single page load.

So you have to be very careful when you store data in the options table. If you’re storing potentially thousands of rows, you have to make sure that you don’t accidentally set up auto load to yes because it will cause drastic consequences.

We had a bug that it was both EDD/WP_Session combined where options, the session data was originally created with auto load of no, which is the way that it should be. But then when the option got updated using the update option function, the auto load got changed to yes.

BRAD: Oops.

PIPPIN: So we would end up with thousands of rows auto loaded and would cause horrific performance problems. And it’s actually what we discovered, there was nowhere where we had accidentally just left off the auto load option, but it turns out the update option function does not allow you to specify auto load for yes or no. And there was some weird thing going on where, when the option was originally created using add option, it would be auto load no. But then when we’d update it, it would become auto load yes, which was a little bit catastrophic for a little bit.

BRAD: Yeah. I imagine, yeah.

PIPPIN: So it’s something that, if you’re using the options table to store transient data or to store temporary data, you need to be careful with it and make sure that you explicitly never set auto load to yes.

BRAD: Right. I guess the other thing that actually WordPress Core actually stores in the WP_Options table is transient, and that’s pretty much, by definition, temporary data as well, right?

PIPPIN: Yeah, I’d say so.

BRAD: Yeah, it’s expiring.

PIPPIN: I mean, it’s very similar. I believe WP_Session originally used transients, I think, and then it changed to regular options.

BRAD: Okay.

PIPPIN: I mean, when you look at it, a transient is nothing more than a row in the options table. It has a unique key set so that it expires.

BRAD: Yes.

PIPPIN: That’s pretty much all it is.

BRAD: Yeah.

PIPPIN: But the data doesn’t actually clean itself out of the database until you call it.

BRAD: Right.

PIPPIN: So when you say get transient, it will go and look and see if the data, if that transient has expired. And if it is, it gets deleted at that time, not some other time before you ever loaded it.

BRAD: Right. Interesting.

PIPPIN: That’s one of the things that WP_Session added as well is it actually has a cron job that will go through and look for expired sessions to delete them, so it auto cleans itself.

BRAD: Right. Yep. Yeah, that makes sense. What do you think about plugins leaving data behind? What’s your position on that?

PIPPIN: First of all, I think your plugin, regardless of whether it’s storing data in custom tables, in options, in post meta, et cetera, should always have a cleanup routine. So when you uninstall a plugin, there should always be a way to remove all data associated with the plugin.

BRAD: Should it — so when you remove a plugin, I don’t think there’s a hook for that. There’s a hook for deactivating, but when —

PIPPIN: It’s not during deactivation. It’s during when you click delete from the plugins menu.

BRAD: Yeah, but what if you delete it from the file system?

PIPPIN: Well, at that point what are you going to do? I mean, it’s not like your code is there anymore, so you can’t run from ghost code.

BRAD: Right, right. But that’s what I’m saying. Do you do it on deactivation then?

PIPPIN: I don’t do it on deactivation. I do it on the actual uninstall hook, so using the uninstall.php file.

BRAD: Okay.

PIPPIN: Are you familiar with that one?

BRAD: What’s that?

PIPPIN: Are you familiar with the uninstall.php file?

BRAD: No.

PIPPIN: Uninstall.php, if you go and look at — if you look at the — there’s an activation hook and a deactivation hook in WordPress, and the deactivation one — now I’m forgetting how it works. If your plugin contains uninstall.php, the file actually called uninstall.php in the main plugin folder, that file will be executed when the plugin is deleted, by default.

BRAD: Okay.

PIPPIN: That’s just how the plugin’s API works. Let’s say that I have a plugin that has a custom table, and I want to delete that table when my plugin is uninstalled. I will create an uninstall.php file, and then I will run the database queries inside of that file to delete my tables.

BRAD: Okay.

PIPPIN: That allows you to do a cleanup process. In EDD, for example, we go through, and we delete our taxonomy terms. We delete the products. We delete any payment records. We delete all of that stuff.

BRAD: What if —

PIPPIN: Now something to be very careful of —

BRAD: Yeah, I think you’re going where I was just going to ask. What if someone doesn’t want to remove everything?

PIPPIN: Something to be very careful of, make sure it’s opt in.

BRAD: Yeah.

PIPPIN: We used to automatically delete all the data when you deleted the plugin. Now WordPress actually gives you a really nice prompt. It auto-detects if you have uninstall.php, and it will give you a prompt that says, “Do you really want to delete this plugin? By the way, this plugin will delete itself and all of its data if you click delete.”

BRAD: That’s cool.

PIPPIN: Well, people don’t actually read, so a lot of times we would run into issues where somebody was trying to delete the plugin and install a different version. They’d go to plugins, find the plugin, deactivate it, and then delete it, and the click the delete button, which would delete all of their data. Then they would install the new version and all their data is gone.

BRAD: Yikes.

PIPPIN: And so if you’re running uninstall.php, and it’s one something that’s actually storing important data, make sure that you have an opt-in system. What you can do is, you can just add an options to your settings page that says, “Do you want to delete data when you uninstall this plugin?” Then in uninstall.php, you check to make sure that that option is set.

BRAD: I feel like it might be better to kind of hook into the deactivate and then present them with like a little prompt that says, “Would you like to delete all your data?” Just kind of reminding them that there’s a bunch of data there.

PIPPIN: Well, but one of the problems with deactivate is, people will deactivate plugins all the time for testing of conflicts or when you install an update for a plugin, it gets deactivated.

BRAD: Right. But I mean it’s just —

PIPPIN: But I don’t actually remember if that fires a deactivation hook.

BRAD: Right, but I just mean like you could just show it and allow them to kill it, to kill the message and just ignore it. Yeah.

PIPPIN: I don’t know. I like the way that WordPress does it with uninstall.php. It doesn’t require that you add any custom prompts or anything like that.

BRAD: Right. Right, right, right. Yeah. I’ll have to check it out.

PIPPIN: They both work. It’s something to play with if you’ve never done it before.

BRAD: Yeah. There’s not a lot of data in my plugin in Migrate DB Pro, so I don’t feel it’s a big deal.

PIPPIN: Yeah. A lot of times I think you can just kind of not worry about it. I mean if your plugin stores, say, one option in the database.

BRAD: Yeah.

PIPPIN: While it might be the responsible developer to go ahead and remove that on uninstall, it’s not a big deal if you don’t. Should you? Maybe. Now if your plugin is storing a ton of data, then if you uninstall, then yes, you should remove that data, as long as you tell people that you’re going to.

BRAD: Right.

PIPPIN: And give them the chance to opt out of it.

BRAD: Yeah. I was thinking of a little plugin idea, a tool that would kind of monitor your queries and determine, over time, options in your WP_Options table that have never been accessed in like the last six months or something.

PIPPIN: That’d be interesting.

BRAD: Then you could take a look at them and say, oh, yeah, that’s garbage; that’s garbage. And just kind of remove the garbage. Have you ever heard of anything like that? Is there something out there like that?

PIPPIN: No. Honestly, I don’t really know how that would work because that would mean that you would need to store something any time data is accessed, right?

BRAD: Well, any time data is accessed in the WP_Options table specifically, you could just set a counter in like a different table somewhere.

PIPPIN: Sure.

BRAD: And increment it, you know.

PIPPIN: I don’t know. I almost wonder. With the exception of some sites, like sites that have cart data or session data stored in options, most people’s options tables are not going to be that large. I mean we’re talking probably 200 rows would be considered large for a lot of sites.

BRAD: Right.

PIPPIN: Maybe. If you disagree with me —

BRAD: Throw in all the temporary data and then we’ll see.

PIPPIN: Well, right, but I’m just thinking for most general sites, like scanning your options table for unused options is not difficult.

BRAD: Right, yeah, yeah.

PIPPIN: Because it’s not usually that large.

BRAD: Right. I think you’re probably right. It’s probably not much.

PIPPIN: If you do have a site that has thousands of rows and options, then sure.

BRAD: I think what happens though is most plugins don’t clean up after themselves, so if you’ve had a site since, you know, 2004 like myself, then ten years of installing plugins and uninstalling plugins that leave a bunch of garbage behind, your WP_Options table could look pretty bad. I’m pretty sure mine does.

PIPPIN: That’s true.

BRAD: I was just kind of thinking of a way to kind of clean the cruft.

PIPPIN: It’s definitely a good idea if you are experiencing that, like where you have a lot of options in there, especially if they’re set to auto load, yes, because it will cause some pretty bad performance issues.

BRAD: Right.

PIPPIN: If your admin is super slow or something like that, certainly.

BRAD: Yeah. Yeah, I’ve been thinking of just migrating my posts table and then reconfiguring all the settings in my site, but that’s a little scary because I could miss something, right? And then everything is broken, and I don’t know how to fix it. Anyways, yeah. I don’t know. I might write that plugin. It’s not a big one or anything.

PIPPIN: It would be a cool plugin for sure.

BRAD: If anybody —

PIPPIN: It’d be very useful for those plugins that needed it, I mean for those sites that needed it.

BRAD: Yeah, yeah. But maybe someone has a solution. They can let us know in the comments.

PIPPIN: Yeah, that would be great. Anything else about custom tables that we want to throw out there?

BRAD: No, I think we covered it. Yeah.

PIPPIN: I think a good summary is, just because a lot of people say don’t ever use custom tables, don’t listen to them. But don’t necessarily go crazy and just writing custom tables all the time.

BRAD: Yeah.

PIPPIN: Think about the way that your plugin or your theme or whatever it is that’s storing custom data — hopefully you’re not creating custom tables and themes. But think about the way that your data is going to be stored and the way that it’s going to be accessed. Think about the query performance, what you need to query, how you might need to change that data over time because if a custom table makes more sense in terms of performance, data accessibility, et cetera, go for it. That’s a perfect reason why you should create a custom table.

BRAD: And especially if you’re building a site for a client or you’re building an app based on WordPress.

PIPPIN: Sure.

BRAD: Really, why not? Right? Why not squeeze some more power out of using a custom table?

PIPPIN: Yeah, you probably want to think about it a little bit more carefully when it comes to building a plugin or a theme that’s being released to the public, whether free or commercial, just because, since you don’t control every aspect of the site, there’s more things to consider.

BRAD: Yeah.

PIPPIN: I mean, whether it’s conflicts with other plugins, user permissions, whatever it may be, there are more points of failure or issue.

BRAD: Yeah.

PIPPIN: There’s more points where you could run into issues.

BRAD: Yep. But that being said, if you have a really good reason to use a custom table, there are other plugins doing it, and take a look at WooCommerce. Take a look at Post 2 Posts. And see what they’re doing.

PIPPIN: And I think something else to consider, and this might be a good conversation for another episode, is what do you do when you realize that you need to change architecture and you need to try and migrate all of that data because that could be a real challenge.

BRAD: Yeah. It’s fun.

PIPPIN: It is fun. I recently did it. I had an update. I completely changed the architecture for my software licensing extension for Easy Digital Downloads. And I had to write an upgrade routine that could theoretically change 10 to 100,000 rows in the database successfully without failing. And that was kind of a daunting task. Luckily it worked, and I succeeded. And so far have not reported a single failure, which was pretty exciting.

BRAD: Yeah.

PIPPIN: But something to consider because if you’re using custom tables or you’re not using custom tables, and you need to swap it to use the other or just to make any kind of architecture changes, like when you’re talking about when you’re storing a lot of data, the way that you store it is really important because it’s going to be there forever.

BRAD: Yep.

PIPPIN: And so if you ever have to change it, it can really bite you in the ass.

BRAD: Yeah. Yep.

PIPPIN: Yeah. Anyway, maybe that’s a conversation for the next episode or two.

BRAD: Yeah, indeed.

PIPPIN: Anything else you want to throw out there, Brad?

BRAD: No, I think we should wrap it up.

PIPPIN: All right. Go for it.

BRAD: All right. Well, thanks, everybody. Until next time.

PIPPIN: Have a good one.