WordPress’s WooCommerce plugin uses a table called wp_woocommerce_order_itemmeta
as an extensible key-value storage mechanism for order line items. Its parent table is wp_woocommerce_order_items
. These two tables work similarly to wp_postmeta
and wp_posts
, and have the same performance issues. In large sites with a long history of orders, lookup may be slow.
wp_woocommerce_order_itemmeta
This DDL, usable only on a modern version (5.7 +) of MySQL or MariaDb, adds high performance keys to the wp_woocommerce_order_itemmeta
table following the same principles that the Index WP MySQL For Speed plugin does with wp_postmeta
, wp_usermeta
and wp_termmeta
.
ALTER TABLE wp_woocommerce_order_itemmeta
ADD UNIQUE KEY meta_id (meta_id),
DROP PRIMARY KEY,
ADD PRIMARY KEY (order_item_id, meta_key, meta_id),
DROP KEY meta_key,
ADD KEY meta_key (meta_key, meta_value(32), order_item_id, meta_id),
ADD KEY meta_value (meta_value(32), meta_id),
DROP KEY order_item_id;
This DDL restores WooCommerce’s standard keys.
ALTER TABLE wp_woocommerce_order_itemmeta
DROP PRIMARY KEY,
ADD PRIMARY KEY (meta_id),
ADD KEY order_item_id (order_item_id),
DROP KEY meta_key,
ADD KEY meta_key (meta_key(32)),
DROP KEY meta_id,
DROP KEY meta_value;
wp_wc_orders_meta
Here’s another table, new with HPOS, that can stand to be rekeyed. Like the others, these keys get rid of prefix keying and exploit InnoDB’s clustered indexing.
ALTER TABLE wp_wc_orders_meta
ADD UNIQUE KEY id (id),
DROP PRIMARY KEY,
ADD PRIMARY KEY (order_id, meta_key, id),
DROP KEY meta_key_value,
ADD KEY meta_key_value (meta_key, meta_value(32), order_id, id),
DROP KEY order_id_meta_key_meta_value;
To put this one back:
ALTER TABLE wp_wc_orders_meta
DROP PRIMARY KEY,
ADD PRIMARY KEY (id),
DROP KEY id,
DROP KEY meta_key_value,
ADD KEY meta_key_value (meta_key(191), meta_value(100)),
ADD KEY order_id_meta_key_meta_value (order_id, meta_key(191), meta_value(100));
wp_automatewoo_log_meta
Stores with the AutomateWoo plugin have this wp_automatewoo_log_meta
table.
This applies the same rekeying principles as the ones mentioned earlier. Props to Leho Kraav for working this out.
ALTER TABLE wp_automatewoo_log_meta
ADD UNIQUE KEY meta_id (meta_id),
DROP PRIMARY KEY,
ADD PRIMARY KEY (log_id, meta_key, meta_id),
DROP KEY meta_key,
ADD KEY meta_key (meta_key, meta_value(32), log_id, meta_id),
ADD KEY meta_value (meta_value(32), meta_id),
DROP KEY log_id;
To put this one back to standard keys:
ALTER TABLE wp_automatewoo_log_meta
DROP PRIMARY KEY,
ADD PRIMARY KEY (meta_id),
ADD KEY log_id (log_id),
DROP KEY meta_key (meta_key),
ADD KEY meta_key (meta_key(191)),
DROP KEY meta_id,
DROP KEY meta_value;
Hi Ollie, discovered your blog today. Thank you so much for sharing your work in a readable and useable way. Was such a long time i did not find a blog which aims to deliver usefull content and not trigger “views” 🙂
Regards from France
Thank you for the kind words.
“`
ALTER TABLE wp_automatewoo_log_meta
ADD UNIQUE KEY meta_id (meta_id),
DROP PRIMARY KEY,
ADD PRIMARY KEY (log_id, meta_key, meta_id),
DROP KEY meta_key,
ADD KEY meta_key (meta_key, meta_value(32), log_id, meta_id),
ADD KEY meta_value (meta_value(32), meta_id),
DROP KEY log_id;
“`
Looks correct to me, thank you Leho!
Heya Ollie.
I think exactly the same treatment can also be applied to AutomateWoo log table:
“`
MariaDB [mysql]> SHOW CREATE TABLE wp_automatewoo_log_meta;
| wp_automatewoo_log_meta | CREATE TABLE `wp_automatewoo_log_meta` (
`meta_id` bigint(20) NOT NULL AUTO_INCREMENT,
`log_id` bigint(20) DEFAULT NULL,
`meta_key` varchar(255) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
`meta_value` longtext COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
PRIMARY KEY (`meta_id`),
KEY `log_id` (`log_id`),
KEY `meta_key` (`meta_key`(191))
) ENGINE=InnoDB AUTO_INCREMENT=5626462 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci |
“`
Hello, Leho!
Have you tested this AutomateWoo solution? I am eager to implement it in production.
Thank you!
I’m not sure Leho reads these comments.
At any rate, this optimization is in the latest version of the Index WP MySQL For Speed plugin.