WooCommerce key improvement

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;

7 thoughts on “WooCommerce key improvement”

  1. 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

    Reply
  2. “`
    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;
    “`

    Reply
  3. 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 |
    “`

    Reply

Leave a Comment