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.
- Use compound clustered primary keys to optimize the most common lookups.
- 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.