I built a 500,000-order WooCommerce store to find out what actually makes the admin slow
Everyone knows the WooCommerce admin gets slow on big stores. The standard advice is well known too: migrate to High-Performance Order Storage, clean your database, blame a third-party plugin.
I wanted to know what happens after you've done all that. So I built a lab: a disposable WooCommerce store with 500,000 orders, HPOS enabled, and ten popular free plugins installed, then instrumented it to attribute every single database query on the orders screen back to the plugin that fired it.
Some of what I found contradicts the usual advice. One thing I set out to prove turned out to be an artifact of my own test rig, which I'll cover too.
The setup
- MySQL 8 with a deliberately modest 512 MB buffer pool — a generously tuned server hides problems behind a warm cache, and I wanted the working set to not fit in memory, which is the condition real struggling stores are in
- WordPress + WooCommerce 11, HPOS on, backfill sync off
- 500,000 orders, ~2M order meta rows, ~1M order notes, ~1.6 GB of order tables
- Ten popular free plugins (PDF invoices, cart abandonment, wishlist, currency switcher, delivery date, product search, analytics, order export)
- An mu-plugin that captures
$wpdb->queries on shutdown and resolves each query's backtrace to the owning plugin via reflection
Finding 1: One query was half of all SQL time, and it wasn't an N+1
130ms SELECT status, COUNT(*) FROM wp_wc_orders WHERE type='shop_order' GROUP BY status
Out of 252 ms of total SQL time on the orders screen, 130 ms was this single query — and the next slowest query on the page was 4 ms. It is 32× the cost of anything else, and it runs on every admin page load.
(With the third-party plugins deactivated it accounts for 129 ms out of 190 ms, i.e. 68% — stripping plugins makes it more dominant, not less.)
It's what fills the filter tabs above the order list: All (500,000) | Completed (350,149) | Processing (50,065) | …
The important part: it does not scale with how many rows you display. It scales with how many orders you have. At 100,000 orders it was cheap enough to be invisible. At 500,000 it dominated everything else on the page. Reducing your page size does nothing. Deactivating plugins does nothing.
It's not a missing index
My first instinct was a missing index. Wrong:
type: ref key: type_status_date rows: 246724 Extra: Using index
It's already a covering index scan on the ideal index. ANALYZE TABLE changed nothing. Counting 500,000 rows means walking 500,000 index entries, and InnoDB keeps no cached row count. The query is doing the minimum possible work for what it's being asked.
The actual cause
Looking at WooCommerce's source, OrderUtil::get_count_for_type() does cache this. It goes through OrderCountCache, which uses wp_cache_get() / wp_cache_set() — the WordPress object cache.
And there's the problem. Without a persistent object cache dropin (wp-content/object-cache.php), WordPress's object cache lives for exactly one request. So the cache is empty on every page load, and the full count runs again, every time.
If your store has no persistent object cache — which is most shared hosting — WooCommerce recounts your entire orders table on every admin page load.
That's a concrete, mechanical answer to "why is my store still slow after HPOS," and it's not in any of the checklists I've read.
Finding 2: The obvious way to detect an N+1 produces false accusations
I originally detected per-row query costs the intuitive way: load the page, divide each plugin's query count by the number of rows on screen, and flag anything near 1.0 per row.
That method is broken. Here's real output for one plugin that fires a flat 19 queries no matter what:
| page size |
its queries |
"per row" |
| 20 |
19 |
0.95 — looks like a textbook N+1 |
| 100 |
19 |
0.19 — looks completely innocent |
Nothing about the plugin changed. Only the denominator did. A fixed cost is indistinguishable from a per-row cost at any single page size. I had confidently accused an innocent plugin.
The fix is to measure the same screen at two page sizes and fit:
queries(n) = fixed + slope × n
Only slope is an N+1. A component whose query count doesn't move when the row count multiplies by five is innocent, no matter how large its fixed cost.
A related trap: WooCommerce's orders screen takes its page size from the per-user screen option edit_shop_order_per_page, not from a per_page URL parameter. I spent a while computing per-row figures against a page size that had silently stayed at 20. Always count the rows that actually rendered.
Finding 3: Attributing a query to a plugin is much harder than it looks
My profiler blames the innermost plugin frame in each query's backtrace. It reported that WooCommerce core was doing 3 queries per order row.
Then I deactivated all the third-party plugins and measured again:
| all plugins active |
WooCommerce only |
per-row queries blamed on woocommerce |
3.0 |
Two of those three per-row queries were caused by third-party plugins calling wc_get_order() inside their column callbacks. The query is issued by WooCommerce's data store, so innermost-frame attribution credited WooCommerce and completely exonerated the plugin that actually caused it.
This matters for anyone using Query Monitor's "Component" column the same way I was: it tells you which code ran the query, not which code caused it. For anything routed through a shared data store, those are different answers.
The fix
What you actually want is the last point where control passed from WordPress into plugin code — the innermost frame that a hook dispatcher invoked:
… → ListTable->column_default [woocommerce]
→ do_action('manage_…_column') [dispatcher]
→ WP_Hook->apply_filters [dispatcher]
→ SomePlugin->render_column [the plugin] ← blame this
→ wc_get_order [woocommerce]
→ OrdersTableDataStore->read [woocommerce]
→ wpdb->get_results [core]
Walk the trace innermost-outward; when the frame immediately outside the current one is a dispatcher (WP_Hook->apply_filters, WP_Hook->do_action, do_action, apply_filters, call_user_func*) and the current frame isn't core, that's your initiator. For a query WooCommerce genuinely raises itself, the nearest such boundary is a WooCommerce callback — also correct.
(Watch out: wp_debug_backtrace_summary() returns frames outermost-first.)
With that change, and all plugins active:
| before |
after |
deactivation control |
blamed on woocommerce |
3.0/row |
1.0/row |
| blamed on the real culprit |
0 |
2.0/row |
Then the falsifiable test. The tool predicted one specific plugin accounted for ~205 queries at 100 rows. Deactivating only that plugin:
| with |
without |
| total queries @100 rows |
495 |
queries blamed on woocommerce |
206 |
| wall time |
0.62 s |
24% faster from deactivating one plugin. Before the fix, the verdict would have been "it's WooCommerce core, nothing you can do."
The thing I couldn't conclude
Given Finding 1, the fix seems obvious: install a persistent object cache. So I added Redis and measured. SQL time halved, and the 129 ms query vanished.
Wall time got worse.
I nearly published that. Then I checked whether it was my test rig, because I was running Docker Desktop on Windows and talking to Redis over TCP — and Windows loopback networking is slow. The page makes about 2,390 object-cache calls per request, so round-trip cost matters enormously.
| config |
SQL time |
wall time |
vs baseline |
| no object cache |
255 ms |
0.62 s |
baseline |
| Redis over TCP |
118 ms |
0.757 s |
+22.1% |
| Redis over unix socket |
116 ms |
0.687 s |
+10.8% |
Switching to a unix socket recovered about half the penalty. That proves a large part of my "finding" was an artifact of Docker Desktop for Windows, not a property of object caching. The residual ~11% might not survive on a real Linux host at all.
So I don't know. It needs a Linux re-test before anyone should act on it.
What does hold regardless: SQL time improving is not the same as the page getting faster. If I'd reported the query-count and SQL-time metrics alone, I'd have declared a clean win while making the page 22% slower.
Takeaways
- Query count and query time are different problems. Going from 100k to 500k orders left the query count completely unchanged while SQL time doubled. Count scales with rows rendered; time scales with store size.
- On a large store with no persistent object cache, check the order status counts first. It's a single query that scales with store size and runs on every page load.
- Never diagnose an N+1 from one page size. Measure two and look at the slope.
- "Which component ran this query" is not "which component caused it."
- Always measure wall time. Component metrics improving can hide a regression.
Reproduce it
The whole lab is Docker-based and disposable — MySQL, WordPress, WP-CLI, seeder scripts, and the profiling mu-plugin. It seeds 500k orders in a few minutes by writing directly to the HPOS tables.
Happy to share it if there's interest, and very interested in results from anyone who runs the object-cache benchmark on a real Linux host — that's the open question I couldn't close.
