When working with WooCommerce’s High‑Performance Order Storage (HPOS), it’s essential to ensure that custom SQL queries reference the correct tables. Recently, I needed to update a summary table (wp_order_itemmeta_summary) that aggregates order item meta data. Here’s an overview of my testing process using WP‑CLI, the queries I ran, what worked, and what didn’t.

Environment Overview

• HPOS is Enabled:
  Order item meta data is stored in the table wp_woocommerce_order_itemmeta rather than the legacy table.
• Summary Table:
  Our summary table is named wp_order_itemmeta_summary.

Step 1. Testing the SELECT Query

First, I wanted to verify that I could aggregate meta keys from the correct order item meta table. I ran:

bashCopywp db query "SELECT meta_key, COUNT(*) AS total FROM wp_woocommerce_order_itemmeta GROUP BY meta_key;"

Result:
The command returned a list of meta keys along with their counts. For example, meta keys such as _add-to-cart had a total of 105650 records. This confirmed that our data exists in wp_woocommerce_order_itemmeta.

Step 2. Testing the TRUNCATE Command

Next, I tested clearing the summary table:

bashCopywp db query "TRUNCATE TABLE wp_order_itemmeta_summary;"

Result:
The command executed successfully, returning:

graphqlCopyQuery OK, 0 rows affected (0.01 sec)

This meant that the summary table was now empty and ready for new data.

Step 3. Testing the INSERT Query

With the summary table empty, I ran the INSERT command to repopulate it with aggregated data:

bashCopywp db query "INSERT INTO wp_order_itemmeta_summary (meta_key, total)
SELECT meta_key, COUNT(*) AS total
FROM wp_woocommerce_order_itemmeta
GROUP BY meta_key;"

Result:
Initially, I received an error 1146 when referencing a wrong table name (e.g. when trying to use the legacy table name). After verifying the correct table name with:

sqlCopySHOW TABLES LIKE '%order_itemmeta%';

I confirmed that the proper source table is wp_woocommerce_order_itemmeta. Updating the INSERT query to reference this table allowed the query to run without error. However, after running the INSERT query, I verified the summary table with:

bashCopywp db query "SELECT * FROM wp_order_itemmeta_summary;"

Observation:
The query returned an empty result set. This indicates that while the syntax was now correct, either the data wasn’t inserted as expected or the summary table structure might need reviewing. (It’s a good idea to double‑check that the summary table has the proper schema to accept the two columns: meta_key and total.)

What Worked

• Correctly Querying the HPOS Table:
  Changing the table name from the legacy name to wp_woocommerce_order_itemmeta allowed the SELECT query to return valid results.
• TRUNCATE Command:
  Running TRUNCATE TABLE wp_order_itemmeta_summary; via WP‑CLI successfully emptied the summary table.
• INSERT Query Syntax:
  Using the correct table name in the INSERT query executed without error once the correct source table was identified.

What Didn’t Work / Next Steps

• Empty Summary Table After INSERT:
  Despite no syntax error in the INSERT query, the summary table remained empty. This outcome suggests one of the following:
  • The summary table’s schema might not match the data types or columns expected (ensure that it has columns for meta_key and total that are compatible with the output of the SELECT query).
  • There may be constraints (or triggers) affecting data insertion.

Next Steps:

1. Check Table Schema:
   Verify the schema of wp_order_itemmeta_summary to ensure it has appropriate column definitions. For example, meta_key should be a string (e.g. VARCHAR) and total should be an integer.
2. Review Database Logs:
   Look for any errors or warnings in your MySQL logs that might indicate why rows weren’t inserted.
3. Test on a Staging Site:
   Run similar queries in a safe, non‑production environment to further isolate the issue.

Conclusion

In our testing:

• The SELECT query worked perfectly when referencing wp_woocommerce_order_itemmeta.
• The TRUNCATE command cleared the summary table as expected.
• Updating the INSERT query to use the correct table name allowed it to execute without error; however, the summary table did not display the expected aggregated data—indicating a need to review the summary table’s schema and constraints.

By methodically testing each component with WP‑CLI and checking results at every step, you can troubleshoot and adapt your queries for HPOS compatibility effectively. This approach is vital as WooCommerce continues to evolve and rely more on its API and custom storage tables.

---

References:

kinsta.com – Kinsta’s tutorial on using get_posts

kinsta.com – Kinsta’s post on retrieving post IDs in WordPress

Feel free to leave comments if you have additional questions or if you’ve encountered similar issues in your environment.