The new HPOS tables

Thanks for the question. WooCommerce’s High Performance Order System (HPOS) tables are defined in their source code here:

https://github.com/woocommerce/woocommerce/blob/trunk/plugins/woocommerce/src/Internal/DataStores/Orders/OrdersTableDataStore.php#L2753

These tables’ key definitions are close to optimal for their purpose, unlike WordPress’s core tables. (Do I dare call those core tables “legacy” tables? They have been around for a couple of decades.) Therefore, I don’t see a need to change their key definitions for performance.

A couple of minor opportunities do present themselves.

1. Use compound clustered primary keys to optimize the most common lookups.
2. There are a couple of places where the InnoDB Barracuda storage engine would allow keys without prefixes even though they are defined with prefixes.

I don’t believe the speedups from implementing these opportunities would justify the changes, though.

I could be wrong about this, of course. I will keep an eye on this part of WooCommerce. If you, or anyone, finds inefficiencies in this subsystem, please use the plugin to upload metadata and monitors showing the problem.

Great. Thanks for answering and info ??

@olliejones, so all wp core tables are safe to add your plugin keys (wp_posts, wp_post_meta, etc…)?
HPOS enabled, sync (compatibility mode) disabled.

tnx for awesome plugin!