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.

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;

3 thoughts on “WooCommerce key improvement”

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