Hey @specialk
Thanks for the inquiry concerning our custom tables. I’d be happy to answer your questions.
First, the tables you specifically listed above are for Action Scheduler, a library that EDD does not use at the moment, so those tables would have been added by a different plugin. Many plugins in the WordPress space use Action Scheduler at this time, as it is great for background queue processing.
So if we ignore those tables since they aren’t ours, we can talk about the EDD specific tables. One of the challenges that EDD has had for years is scaling. Previously we used the Posts table and postmeta for data that didn’t really ‘fit’ in the main posts table, because it was the ‘WordPress’ way. However, as a store grows over time, we end up pushing data into tables that are not structured for the dataset, leading us to run into performance issues when querying for this data later on (like reporting).
For instance, an ‘order’ doesn’t really have all of the same columns that a ‘post’ does, so a bulk of the data was put into meta tables. As that trend continues, your result is millions of rows in the database in meta. Meta tables are notoriously bad for performance especially when we start talking about complex queries for reporting, as every ‘filter’ just results in adding another million rows to the end query.
Moving to custom tables in the format we did was very intent driven. Each table has the columns necessary for itself, has typing specific to the data in the column, and typically only contains a ‘meta’ table so that developers can continue to extend EDD in the way they have always expected to. So why so many different tables? This was done to achieve a few things:
- EDD core should aim to add no data to meta tables. It is our goal to avoid finding it necessary to add a row into a meta table for EDD core alone. Extensions may take advantage of this but, the mission was to avoid putting two rows into the database for an object, when one would do. This means that for almost all the queries in EDD core, we can avoid doing expensive meta queries to filter the results.
- Performance. It may seem on the surface that some of these new data objects could share tables for instance: file download logs, api request logs, and generic logging. It could have been done to make all three of these live in the same table, however, the file download log is historically going to be one of the larger tables, and one of the most used. Therefore breaking it out into it’s own table was done to ensure that we can handle a larger scaling effort, even as a store delivers millions of file downloads over time. And, by knowing how we query that data specifically by columns, leads us into the 3rd point…
- Proper Indexes. One of the things that we can do to help database performance at scale is to have proper indexes on the tables. The reason we don’t frequently include more than one data type into a single table is so that we can add proper indexes based on the queries we know that we’ll end up using the most. If you put data into tables that don’t share a consistent query structure when retrieving it, then we’re producing a table that will inherently succumb to performance issues as the table grows in size.
The reality is that most WordPress plugins don’t hold critical financial and transactional data, like an eCommerce Platform. Most of the data in an eCommerce database has a specific reason for being maintained. A single purchase produces an order, a customer, a list of items ordered, possibly a discount used, the log entry for someone downloading the file, any address information supplied by the customer, the confirmation of the transaction ID from the gateway, and even more if there are things like subscriptions, or licenses involved.
All of these data points are extremely different in data structure and formatting, however all of them are important to an eCommerce business. Because they are so different, they need their own table to properly hold this information in a way that can scale.
The other aspect of EDD that we’d heard for years is that the reporting was limited. The simple fact was, that it was due to our database structure being entirely meta driven and non-typed. When we set out to build EDD 3.0’s tables, we wanted to do it in a way that would help us fulfill the many reporting requests that we had heard for years. A great example of this is, what it takes to run an earnings report for a given time.
In EDD 2.0, we would have had to go get all payments in the date range, then find all of their meta entries for the purchase total, then loop through each of them in PHP to get the sum of those values. In EDD 3.0 (this is slightly over simplified for some more advanced reports) however, we can simply use MySQL SUM functions, so that our reports can get the value directly from MySQL instead of using expensive and time consuming foreach loops in PHP.
I can understand the initial concern for the number of tables created, as it is more than most plugins will ever add. No table was added to the custom set without intent and we were selective with what tables we did add asking ourselves if the data in these custom tables would dramatically benefit from it’s own table, or if it could exist in a table with some other data. The adjustments table is a great example of this. Taxes, Fees, and Discounts are all stored in the adjustments table, because at the end of the day they all share a commonality in their structure and purpose. Initially we had split all three of these into their own tables, but after some testing and discussion we determined they could live in a single table and meet the needs of a store just fine, and still maintain the performance improvements we were aiming for.
I’m happy to answer any more questions you may have concerning this, but I hope that this long-form response gives you some insight into our through process and intent for long-term store performance and scaling.
