High-Performance Order Storage (HPOS) is WooCommerce’s new custom order tables system that replaces the traditional WordPress post meta storage. This guide covers everything you need to know about migrating to and optimizing HPOS.

Migration Steps

1. Code Updates Required

When converting to HPOS, you’ll need to update several types of code:

phpCopy// Old way (pre-HPOS)
add_filter('manage_edit-shop_order_columns', function($columns) {
    // column code
});

// New way (HPOS compatible)
add_filter('manage_woocommerce_page_wc-orders_columns', function($columns) {
    // column code
});

For meta boxes:

phpCopy// Old way
add_meta_box('custom_box', 'Title', 'callback', 'shop_order');

// New way
add_meta_box('custom_box', 'Title', 'callback', wc_get_page_screen_id('shop-order'));

2. Database Optimization

Ensure your MySQL/MariaDB is optimized for HPOS:

  1. Check indexes:
sqlCopySHOW INDEXES FROM wp_wc_orders;

Key indexes should include:

  • PRIMARY on id
  • status
  • type_status_date (composite index)
  • date_created
  • customer_id_billing_email
  1. Optimize tables:
sqlCopyANALYZE TABLE wp_wc_orders;
OPTIMIZE TABLE wp_wc_orders;

3. Performance Optimization

For MySQL 5.7 (Local Development)

Add to wp-config.php:

phpCopydefine('WC_HPOS_DEBUG_FORCE_INDEX', true);

For MySQL 8.0+ or MariaDB 10+ (Production)

The query optimizer should handle HPOS queries efficiently without additional configuration.

4. Common Issues and Solutions

Slow Queries

If seeing slow queries, verify:

  1. Proper indexes are in place
  2. Database statistics are up to date
  3. Query optimizer is using the correct indexes

Check query execution with:

sqlCopyEXPLAIN SELECT COUNT(DISTINCT wp_wc_orders.id) 
FROM wp_wc_orders 
WHERE status IN ('wc-pending','wc-processing'...)
AND type = 'shop_order';

Code Compatibility

Remember to:

  1. Update all direct post meta access to use HPOS methods
  2. Use wc_get_order() instead of get_post()
  3. Update hooks and filters to use new HPOS-compatible names

Best Practices

1. Database Maintenance

Regular maintenance tasks:

sqlCopyANALYZE TABLE wp_wc_orders;
OPTIMIZE TABLE wp_wc_orders;

2. Code Updates

Always use HPOS-compatible methods:

phpCopy// Good
$order->get_meta('_custom_field');

// Avoid
get_post_meta($order_id, '_custom_field', true);

3. Debugging

Enable WordPress debug logging in wp-config.php:

phpCopydefine('WP_DEBUG', true);
define('WP_DEBUG_LOG', true);

Development vs Production Considerations

Local Development (MySQL 5.7)

  • Use WC_HPOS_DEBUG_FORCE_INDEX
  • Expect some query optimizer limitations
  • Focus on code compatibility

Production (MySQL 8.0+ / MariaDB 10+)

  • Better query optimization out of the box
  • Regular database maintenance
  • Monitor performance with tools like Query Monitor

Resources

  • WooCommerce HPOS Documentation
  • MySQL/MariaDB Performance Tuning Guides
  • WordPress Debug Tools

Remember to always test HPOS changes thoroughly in a staging environment before deploying to production.