WooCommerce HPOS MySQL Large Store Profile

Deep local profiling of WooCommerce on MySQL 8.4 with HPOS enabled and fast-store settings. The fixture used 5,001,412,608 apparent schema bytes, 100,000 HPOS orders, 1,500 products, and 1,000 customers.

5,001,412,608MySQL schema bytes
100,000HPOS orders before load
19,628completed measured WP requests logged
1.2%benchmark flow timeout rate
0.0%HTTP 4xx/5xx response rate
149InnoDB row lock waits

Executive Readout

Charts

Slowest Customer Routesp95 wall time from completed WordPress requests.Customer: checkout AJAX order submit1.64sCustomer: shop archive1.25sCustomer: product search1.24sCustomer: add to cart821msCustomer: home/product page818msCustomer: cart page711msCustomer: checkout page682msCustomer: shop page305ms
Slowest Admin Routesp95 wall time. Log scale because order search dwarfs the other admin routes.Admin: HPOS order search61.98sAdmin: edit order1.28sAdmin: orders list1.15sAdmin: orders list, filtered1.03s
Top MySQL Statement DigestsCumulative MySQL time across all load cells. Log scale.HPOS order search ids7,239.30sHPOS order search count48.30sOrder status counts48.08sOptions point lookup34.46sProduct search query28.38sSELECT `option_name` , `option_value` FROM `19.01sSELECT `option_name` , `option_value` FROM `12.93sLoad HPOS order meta11.84sProduct search query11.56sLoad post meta10.50sWoo session write/read10.09sSELECT `o` . `id` AS `o_id` , `p` . `id` AS 9.21s
Top Table I/O WaitCumulative Performance Schema table I/O wait.wp_wc_orders7,369.54swp_wc_orders_meta6,750.40swp_woocommerce_order_items297.73swp_options34.38swp_posts13.47swp_wc_order_operational_data7.85swp_woocommerce_sessions6.49swp_postmeta5.83swp_woocommerce_order_itemmeta5.12swp_wc_order_addresses2.62s
Route p95 Wall Time vs Average DB TimeLog scales. Admin route points are red; customer route points are blue.p95 wall timeaverage DB timeAdmin: HPOS order search: p95 61.98s, avg DB 32.61sHPOS order searchAdmin: edit order: p95 1.28s, avg DB 325msAdmin: orders list: p95 1.15s, avg DB 292msAdmin: orders list, filtered: p95 1.03s, avg DB 278msCustomer: checkout AJAX order submit: p95 1.64s, avg DB 237mscheckout AJAX order submitCustomer: shop archive: p95 1.25s, avg DB 70msCustomer: product search: p95 1.24s, avg DB 71msproduct searchCustomer: add to cart: p95 821ms, avg DB 87msCustomer: home/product page: p95 818ms, avg DB 78msCustomer: cart page: p95 711ms, avg DB 74msCustomer: checkout page: p95 682ms, avg DB 74msCustomer: shop page: p95 305ms, avg DB 22ms100ms1.00s10.00s60.00s10ms100ms1.00s10.00s30.00s

How The Test Was Conducted

  1. Started a fresh local MySQL 8.4 instance with Performance Schema and slow query logging enabled.
  2. Installed WordPress 7.0 and WooCommerce 10.8.1.
  3. Enabled HPOS as the authoritative order store, disabled HPOS compatibility sync, disabled WP-Cron, disabled WooCommerce nonessential admin/task-list features, enabled guest checkout, and used relaxed MySQL durability settings for speed.
  4. Generated products, customers, 100,000 historical HPOS orders, order items, order addresses, and order stats, then inserted high-entropy HPOS order meta until the schema directory reached the 5GB target.
  5. Ran catalog, storefront/cart/checkout, admin-order, and checkout-heavy mixes at 4, 16, 32 concurrent requests for 45s per cell after warm-up.

Slowest Customer Routes

RouteRequestsAvg wallp95 wallMax wallAvg DBAvg queries
Customer: checkout AJAX order submit 1,739 737ms 1.64s 2.27s 237ms 261.69
Customer: shop archive 1,464 490ms 1.25s 2.27s 70ms 76.00
Customer: product search 3,466 509ms 1.24s 2.33s 71ms 75.00
Customer: add to cart 2,753 230ms 821ms 1.56s 87ms 69.00
Customer: home/product page 3,879 233ms 818ms 1.86s 78ms 76.44
Customer: cart page 1,014 184ms 711ms 1.56s 74ms 70.00
Customer: checkout page 3,478 170ms 682ms 1.32s 74ms 75.50
Customer: shop page 1,464 61ms 305ms 815ms 22ms 42.00

Slowest Admin Routes

RouteRequestsAvg wallp95 wallMax wallAvg DBAvg queries
Admin: HPOS order search 10 44.97s 61.98s 61.98s 32.61s 119.40
Admin: edit order 132 480ms 1.28s 1.90s 325ms 142.96
Admin: orders list 121 449ms 1.15s 1.69s 292ms 126.00
Admin: orders list, filtered 108 439ms 1.03s 2.15s 278ms 146.00

Query Hotspots

Query groupCountTotal MySQL timeMaxRows examinedDigest sample
HPOS order search ids 135 7,239.30s 71.26s 13,660,367 SELECT `wp_wc_orders` . `id` FROM `wp_wc_orders` WHERE ? = ? AND ( `wp_wc_orders` . `status` IN (...) ) AND ( `wp_wc_orders` . `type` = ? ) AND ( ( `wp_wc_orders` . `transaction_id` LIKE ? OR `wp_wc_orders` . `billing_email` LIKE ? OR `wp_wc_orders` . `id` IN ( SELECT `search_query_meta` . `order_id` FROM `wp_wc_orders_meta` AS `search_query_meta` WHERE `search_query_meta` . `meta_key` IN (...) AND `search_query_meta` . `meta_value` LIKE ? GROUP BY `search_query_meta` . `order_id` ) OR `wp_wc_orders` . `id` IN ( SE
HPOS order search count 88 48.30s 2.25s 8,910,945 SELECT COUNT ( * ) FROM `wp_wc_orders` WHERE ? = ? AND ( `wp_wc_orders` . `status` IN (...) ) AND ( `wp_wc_orders` . `type` = ? ) AND ( ( `wp_wc_orders` . `transaction_id` LIKE ? OR `wp_wc_orders` . `billing_email` LIKE ? OR `wp_wc_orders` . `id` IN ( SELECT `search_query_meta` . `order_id` FROM `wp_wc_orders_meta` AS `search_query_meta` WHERE `search_query_meta` . `meta_key` IN (...) AND `search_query_meta` . `meta_value` LIKE ? GROUP BY `search_query_meta` . `order_id` ) OR `wp_wc_orders` . `id` IN ( SELECT `orde
Order status counts 492 48.08s 831ms 49,808,309 SELECT `status` , COUNT ( * ) AS `count` FROM `wp_wc_orders` WHERE `type` = ? GROUP BY `status`
Options point lookup 607,959 34.46s 314ms 177,038 SELECT `option_value` FROM `wp_options` WHERE `option_name` = ? LIMIT ?
Product search query 3,466 28.38s 300ms 5,657,851 SELECT SQL_CALC_FOUND_ROWS `wp_posts` . `ID` FROM `wp_posts` WHERE ? = ? AND ( `wp_posts` . `ID` NOT IN ( SELECT `object_id` FROM `wp_term_relationships` WHERE `term_taxonomy_id` IN (...) ) ) AND ( ( ( `wp_posts` . `post_title` LIKE ? ) OR ( `wp_posts` . `post_excerpt` LIKE ? ) OR ( `wp_posts` . `post_content` LIKE ? ) ) AND ( ( `wp_posts` . `post_title` LIKE ? ) OR ( `wp_posts` . `post_excerpt` LIKE ? ) OR ( `wp_posts` . `post_content` LIKE ? ) ) AND ( ( `wp_posts` . `post_title` LIKE ? ) OR ( `wp_posts` . `post_e
SELECT `option_name` , `option_value` FROM `wp_options` WHERE `option_name` IN ( 203,941 19.01s 310ms 226,418 SELECT `option_name` , `option_value` FROM `wp_options` WHERE `option_name` IN (...)
SELECT `option_name` , `option_value` FROM `wp_options` WHERE `autoload` IN (... 19,760 12.93s 208ms 6,434,641 SELECT `option_name` , `option_value` FROM `wp_options` WHERE `autoload` IN (...)
Load HPOS order meta 30,137 11.84s 309ms 166,964 SELECT `id` , `order_id` AS `object_id` , `meta_key` , `meta_value` FROM `wp_wc_orders_meta` WHERE `order_id` IN (...)
Product search query 1,464 11.56s 60ms 4,392,000 SELECT SQL_CALC_FOUND_ROWS `wp_posts` . `ID` FROM `wp_posts` WHERE ? = ? AND ( `wp_posts` . `ID` NOT IN ( SELECT `object_id` FROM `wp_term_relationships` WHERE `term_taxonomy_id` IN (...) ) ) AND ( ( `wp_posts` . `post_type` = ? AND ( `wp_posts` . `post_status` = ? ) ) ) GROUP BY `wp_posts` . `ID` ORDER BY `wp_posts` . `menu_order` ASC , `wp_posts` . `post_title` ASC LIMIT ?, ...
Load post meta 56,317 10.50s 314ms 5,759,134 SELECT `post_id` , `meta_key` , `meta_value` FROM `wp_postmeta` WHERE `post_id` IN (...) ORDER BY `meta_id` ASC
Woo session write/read 8,983 10.09s 246ms 0 INSERT INTO `wp_woocommerce_sessions` ( `session_key` , `session_value` , `session_expiry` ) VALUES (...) ON DUPLICATE KEY UPDATE `session_value` = VALUES ( `session_value` ) , `session_expiry` = VALUES ( `session_expiry` )
SELECT `o` . `id` AS `o_id` , `p` . `id` AS `p_id` , `o` . * , `b` . `id` AS `b_ 30,139 9.21s 257ms 45,807 SELECT `o` . `id` AS `o_id` , `p` . `id` AS `p_id` , `o` . * , `b` . `id` AS `b_id` , `b` . `order_id` AS `b_order_id` , `b` . `address_type` AS `b_address_type` , `b` . `first_name` AS `billing_first_name` , `b` . `last_name` AS `billing_last_name` , `b` . `company` AS `billing_company` , `b` . `address_1` AS `billing_address_1` , `b` . `address_2` AS `billing_address_2` , `b` . `city` AS `billing_city` , `b` . `state` AS `billing_state` , `b` . `postcode` AS `billing_postcode` , `b` . `country` AS `billing_countr
SELECT * FROM `wp_posts` WHERE `ID` = ? LIMIT ? 87,641 6.78s 224ms 82,467 SELECT * FROM `wp_posts` WHERE `ID` = ? LIMIT ?
UPDATE `wp_options` SET `option_value` = ? , `autoload` = ? WHERE `option_name` 1,739 6.61s 109ms 1,739 UPDATE `wp_options` SET `option_value` = ? , `autoload` = ? WHERE `option_name` = ?
INSERT INTO `wp_wc_orders_meta` ( `order_id` , `meta_key` , `meta_value` ) VALUE 10,562 6.03s 217ms 0 INSERT INTO `wp_wc_orders_meta` ( `order_id` , `meta_key` , `meta_value` ) VALUES (...)
SELECT `p` . `ID` FROM `wp_posts` `p` WHERE `post_type` = ? AND `p` . `post_titl 61,878 5.81s 109ms 0 SELECT `p` . `ID` FROM `wp_posts` `p` WHERE `post_type` = ? AND `p` . `post_title` = ? AND `p` . `post_status` IN (...) ORDER BY `p` . `post_date_gmt` ASC LIMIT ?, ...
INSERT INTO `wp_wc_orders` ( `date_updated_gmt` , `id` , `type` ) VALUES (...) O 11,933 5.05s 339ms 0 INSERT INTO `wp_wc_orders` ( `date_updated_gmt` , `id` , `type` ) VALUES (...) ON DUPLICATE KEY UPDATE `date_updated_gmt` = VALUES ( `date_updated_gmt` ) , `id` = VALUES ( `id` ) , `type` = VALUES ( `type` )
INSERT INTO `wp_woocommerce_order_itemmeta` ( `order_item_id` , `meta_key` , `me 15,651 4.67s 249ms 0 INSERT INTO `wp_woocommerce_order_itemmeta` ( `order_item_id` , `meta_key` , `meta_value` ) VALUES (...)

What Would Make It Faster

Order Admin Search

  • Avoid the broad HPOS order search query that ORs across transaction ID, billing email, HPOS meta, and order item name with LIKE.
  • Dispatch by search shape: numeric order IDs should become direct ID lookups; email-like values should use exact or prefix email lookups; transaction IDs should use exact or prefix lookups.
  • Move fuzzy order search to a dedicated search index table or external search service. Keep bounded, indexed columns for order number, billing email, transaction ID, customer names, and selected searchable meta.
  • Put minimum-length, debounce, and cancellation behavior in the admin UI so broad searches do not leave long PHP/MySQL requests running.

Order Counts And Admin Lists

  • Cache or maintain order status counts instead of repeatedly counting the whole HPOS order table for admin menu/list badges.
  • Keep default order list queries bounded by indexed date/status columns and avoid joining or scanning order meta unless the active filter requires it.
  • Consider archival or partitioning strategies for old order/meta rows on very large stores, especially when admin workflows rarely need the entire history inline.

Checkout And Customer Routes

  • Checkout AJAX averaged about 261.69 queries/request for the slowest customer route; reduce repeated option, session, postmeta, and order-meta work on checkout submission.
  • Use a persistent object cache in production. This run intentionally had no persistent object cache, which makes repeated options and metadata loads visible.
  • Offload emails, Action Scheduler work, analytics, and nonessential post-checkout work out of the request path.

MySQL And Data Shape

  • The test used a 1.5GB InnoDB buffer pool against a 5GB schema; a production store should size the buffer pool closer to the hot working set.
  • Keep arbitrary HPOS order meta from becoming the search surface. Large unbounded meta_value rows make useful indexes difficult and amplify I/O.
  • After fixing the query shape, rerun without profiling overhead such as SAVEQUERIES, slow-query logging, and Performance Schema consumers to confirm production impact.

Artifacts

Data files: route-summary.csv, query-digests.csv, table-io.csv, aggregate.csv, results.csv, manifest.json, findings.json.

Source run: /Users/admin/wordpress-sqlite-benchmark-runner/artifacts/woocommerce-hpos-mysql-profile/runs/20260613T010902Z.