This is the first part of a series of deep dives dedicated to explaining the implementation of High-Performance Order Storage. Please see the introduction and overall timeline in the preamble.
There are minimal changes to the database schema compared to the version proposed in January. We have added and removed a few columns in different places, but the overall table structure remains the same as it was described in the first proposal:
There are 4 tables we’ve added during this project:
This solution should speed up data writing and reading considerably: Previously, each new order required one INSERT for the record in the posts table + almost 40 INSERTs for each postmeta record. The new data structure should require at most 5 INSERTs.
For many order searches, we had to join the postmeta table multiple times with the posts table (e.g. to search across address fields and customer email addresses). Now, the searches require fewer joins and the joined tables are considerably smaller compared to the postmeta table.
Please see the up-to-date schema description in the sections below, along with the highlighted changes from the proposal.
Main Order Table
The Main Order Table holds all the data previously stored in the wp_posts table and it adds columns for meta values that are used most frequently.
Since the last proposal, we’ve removed the post_id column as wc_order.id will always match the respective post.ID, so post_id is no longer needed.
We’ve also added:
typecolumn to properly support refunds (which are a type of order and modeled as children of the parent order, as it used to be with posts).customer_notecolumn to store notes customers can leave during the checkout process
| Column Name | Type | Description | Example values |
|---|---|---|---|
| id | bigint(20) unsigned | Unique ID for order. | 1, 5, 143 |
| status | varchar(20) | Status for the order. | ‘wc-processing’, ‘wc-completed’, ‘wc-refunded’ |
| currency | varchar(10) | Currency identifier in which payment was received. | ‘GBP’, ‘USD’, ‘ZAR’ |
| type | varchar(20) | ‘shop_order’, ‘shop_order_refund’ | |
| tax_amount | decimal(26,8) | Total order tax amount | 12.45, 5.0 |
| total_amount | decimal(26,8) | Total order total, including tax | 15.66, 10.0 |
| customer_id | bigint(20) unsigned | Customer ID (0 in case of guest checkout) | 1, 5, 12 |
| billing_email | varchar(320) | Billing email for customer | ‘john@doe.com’ |
| date_created_gmt | datetime | Timestamp when order was created | ‘2021-12-31 23:59:59.999999’, ‘2022-04-31 23:59:59’ |
| date_updated_gmt | datetime | Timestamp when the order or any of its properties were last updated | ‘2021-12-31 23:59:59.999999’, ‘2022-04-31 23:59:59’ |
| parent_order_id | bigint(20) unsigned | Order ID of the parent, in case this order is a refund | |
| payment_method | varchar(100) | Identifier of payment method | ‘bacs’, |
| payment_method_title | text | Title of payment method used | ‘Direct Bank Transfer’, |
| transaction_id | varchar(100) | Transaction ID provided by the payment gateway. | ‘ch_1HbohKHMqnIERF7AtGikC32B’ |
| ip_address | varchar(100) | IP address used to create the order | ‘1.2.3.4’ |
| user_agent | text | The user-agent string of the web client that was used to create the order. | ‘Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.121 Safari/537.36’, ‘Scrapy/2.6 (+https://scrapy.org)’ |
| customer_note | text | Stores the customer note added during the checkout flow. | ‘Call me on my cell once you ship the widget please’ |
Code
CREATE TABLE $wpdb->prefix.'wc_orders' (
id BIGINT(20) UNSIGNED,
status VARCHAR(20) NULL,
currency VARCHAR(10) NULL,
type VARCHAR(20) NULL,
tax_amount DECIMAL(26, 8) NULL,
total_amount DECIMAL(26, 8) NULL,
customer_id BIGINT(20) UNSIGNED NULL,
billing_email VARCHAR(320) NULL,
date_created_gmt DATETIME NULL,
date_updated_gmt DATETIME NULL,
parent_order_id BIGINT(20) UNSIGNED NULL,
payment_method VARCHAR(100) NULL,
payment_method_title TEXT NULL,
transaction_id VARCHAR(100) NULL,
ip_address VARCHAR(100) NULL,
user_agent TEXT NULL,
customer_note TEXT NULL,
PRIMARY KEY (id),
KEY status (status),
KEY date_created (date_created_gmt),
KEY customer_id_billing_email (customer_id, billing_email),
KEY billing_email (billing_email),
KEY type_status (type, status),
KEY parent_order_id (parent_order_id),
KEY date_updated (date_updated_gmt)
);
Order Address Table
The Order Address Table is used to store billing and shipping addresses for the end customers.
We’ve left out the date_created_gmt from the table as that information will be stored in the main order table.
| Column Name | Type | Description | Example Values |
|---|---|---|---|
| id | bigint(20) unsigned | Unique ID for order address record. | 1, 5 |
| order_id | bigint(20) unsigned | Order ID. | 3, 8 |
| address_type | varchar(20) | Type of address (billing, shipping, etc). | ‘billing’, ‘shipping’ |
| first_name | text | First name | ‘John’ |
| last_name | text | Last name | ‘Doe’ |
| company | text | Company name | ‘John Doe Ltd.’ |
| address_1 | text | Address line 1 | ‘Oaktree rd.’ |
| address_2 | text | Address line 2 | |
| city | text | City | ‘New York’ |
| state | text | State | ‘NY’ |
| postcode | text | Postcode/ZIP Code | ‘10019’, ‘E14 3QS’ |
| country | text | Country code from i18n/countries.php | ‘US’, ‘UK’ |
| varchar(320) | ‘john@doe.com’ | ||
| phone | varchar(100) | Phone number | ‘1-500-2345’ |
Code
CREATE TABLE $wpdb->prefix.'wc_order_addresses' (
id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT(20) UNSIGNED NOT NULL,
address_type VARCHAR(20) NULL,
first_name TEXT NULL,
last_name TEXT NULL,
company TEXT NULL,
address_1 TEXT NULL,
address_2 TEXT NULL,
city TEXT NULL,
state TEXT NULL,
postcode TEXT NULL,
country TEXT NULL,
email VARCHAR(320) NULL,
phone VARCHAR(100) NULL,
KEY order_id (order_id),
UNIQUE KEY address_type_order_id (address_type, order_id),
KEY email (email),
KEY phone (phone)
);
Order Operational Table
The Order Operational Table stores fields and flags that we use to maintain the internal order state. We are keeping these fields separate from the core order table as these fields may be changed in the future based on internal implementation details.
The operational table saw one column renamed (download_permissions_granted got renamed to download_permission_granted, notice the missing s in permissions) and one addition of the column recorded_sales as it’s stored for most orders but was omitted in the original proposal.
| Column Name | Detail | Description | Example |
|---|---|---|---|
| id | bigint(20) unsigned | 1, 5 | |
| order_id | bigint(20) unsigned | 3, 8 | |
| created_via | varchar(100) | The identifier for order creation source. WC core uses the following values: – admin – checkout – rest-api – store-api | ‘admin’, ‘checkout’ |
| woocommerce_version | varchar(20) | WooCommerce version which was active when creating the order. Postmeta previously called `_order_version` | ‘4.6.0’, ‘6.7.0’ |
| prices_include_tax | tinyint(1) | For internal use. Whether prices included taxes when purchased. | 0, 1 |
| coupon_usages_are_counted | tinyint(1) | For internal use. Whether coupon usage is counted. | 0, 1 |
| download_permission_granted | tinyint(1) | Marks whether download permissions are granted to order or not. | 0, 1 |
| cart_hash | varchar(100) | Hash of cart contents, used when clearing cart when order payment is successful. | ‘010ae06d098de5f270bd27cc69721576’ |
| new_order_email_sent | tinyint(1) | Whether an email is sent for the order | 0, 1 |
| order_key | varchar(100) | Key used to allow anonymous order access for payment and related operations. | ‘wc_order_jIe6po8pcXvZh’ |
| order_stock_reduced | tinyint(1) | Whether the stock is reduced for order | 0, 1 |
| date_paid_gmt | datetime | Timestamp when the order was first moved into any of the paid statuses. | ‘2021-12-31 23:59:59.999999’, ‘2022-04-31 23:59:59’ |
| date_completed_gmt | datetime | Timestamp when the order was last moved into completed status. | ‘2021-12-31 23:59:59.999999’, ‘2022-04-31 23:59:59’ |
| shipping_tax_amount | decimal(26,8) | Total shipping tax | 12.34 |
| shipping_total_amount | decimal(26,8) | Total shipping amount, including tax | 45.69 |
| discount_tax_amount | decimal(26,8) | Total discount tax | 54.23 |
| discount_total_amount | decimal(26,8) | Total discount amount, including tax | 12.23 |
| recorded_sales | tinyint(1) | Stores information about whether sales were recorded. | 0, 1 |
Code
CREATE TABLE $wpdb->prefix.'wc_order_operational_data' (
id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT(20) UNSIGNED NULL,
created_via VARCHAR(100) NULL,
woocommerce_version VARCHAR(20) NULL,
prices_include_tax TINYINT(1) NULL,
coupon_usages_are_counted TINYINT(1) NULL,
download_permission_granted TINYINT(1) NULL,
cart_hash VARCHAR(100) NULL,
new_order_email_sent TINYINT(1) NULL,
order_key VARCHAR(100) NULL,
order_stock_reduced TINYINT(1) NULL,
date_paid_gmt DATETIME NULL,
date_completed_gmt DATETIME NULL,
shipping_tax_amount DECIMAL(26, 8) NULL,
shipping_total_amount DECIMAL(26, 8) NULL,
discount_tax_amount DECIMAL(26, 8) NULL,
discount_total_amount DECIMAL(26, 8) NULL,
recorded_sales TINYINT(1) NULL,
UNIQUE KEY order_id (order_id),
UNIQUE KEY order_key (order_key)
);
Order Meta Table
This table is similar in functionality to the wp_postmeta table, and acts as a data store for extensions’ data (unless the extension create their own tables).
In contrast to the previous proposal, we’ve decided to leave out date_created_gmt and date_updated_gmt as these will be stored in the parent order.
| Column Name | Type | Description | Example Values |
|---|---|---|---|
| id | bigint(20) unsigned | Unique ID for meta record. | 1, 5 |
| order_id | bigint(20) unsigned, indexed | Corresponding order ID. | 3, 8 |
| meta_key | varchar(255) | Name of the key. | ‘_stripe_customer_id’ |
| meta_value | text | Value of the record. | ‘cus_ICD8cAbkdVKbZF’ |
Code
CREATE TABLE $wpdb->prefix.'wc_orders_meta' (
id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT(20) UNSIGNED NULL,
meta_key VARCHAR(255),
meta_value TEXT NULL,
KEY meta_key_value (meta_key, meta_value(100)),
KEY order_id_meta_key_meta_value (order_id, meta_key, meta_value(100))
)
A note on Order Notes
We’ve decided to remove the order notes from the scope of the project as we don’t consider them a bottleneck for most order-related database operations. We also don’t think the wp_comments table where they are currently stored usually receives a huge amount of write traffic that would compete with order notes. As we’ve decided to enforce the invariant that order id in posts table = order id in the new table, we don’t need to rebind the order notes to point to correct orders. We plan to address the order notes if the need arises based on further performance tests and feedback from the community.
Order Items
Order Items already have their own dedicated tables and they’re out of scope for this project.
Indexes
The current indexes could be further revised based on query and performance analysis and feedback from the community.
Feedback
We are happy to hear the feedback on the database schema and our assumptions presented in this post. Please leave us a comment below!
Leave a Reply