Monitoring doesn’t work

Thanks for the report. Is it possible you started your monitor and did not give it time to finish before looking at it? That’s one reason you might see a zero duration.

As for WooCommerce’s so-called “high performance” order store, yes, the standard keys are stuck in the year 2015 with MySQL 5.6 along with the rest of WordPress. They use prefix keys on VARCHAR(255) columns. They can be improved. More to come.

Really, WooCommerce?

I’ve just restarted a monitor test for 1 min, and even if I look after 5min I still have 0s and 0 query.

Yes, and almost 10M wp_postmeta lines correspond to shop_order wp_posts. That’s a lot of lines for WooCommerce. I have about 150,000 orders and it’s growing every day.

So you don’t have any tips for WooCommerce tables?

Sorry to jump in. I just wanted to give my +1 for optimizing wp_woocommerce_order_itemmeta

Sorry to say, I don’t know why your site doesn’t capture monitors. Puzzling. I would next look at wp-content/debug.log for clues.

I’ll take a look at the “HPOS” stuff. It would help to get some monitors showing its use.

Here’s an update to the keys on that wp_woocommerce_order_itemmeta table. It creates the same suite of keys as this plugin does for wp_postmeta. It requires a modern (5.7+) version of MySQL or MariaDB. It replaces the clustered index (PK) with a more frequently-accessed one.

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;

If you want to restore the WooCommerce standard keys, do this.

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;

• This reply was modified 12 months ago by OllieJones.

That’s great, thanks @olliejones. Would you like me to upload a monitor before making the change and then again after? Or will uploading a single monitor afterwards suffice?

Before-and-after monitors would be very useful! Thanks for offering to do that extra work.

I haven’t heard back from you, so I’m marking this resolved. Please don’t hesitate to create a new support thread if I can help you further.