Finding the Bottleneck: Database Query Analysis

In Part 2 we confirmed that performance was broken — 23-second response times, 23% error rates, exponential degradation under load. The server had plenty of CPU and RAM. So where was the bottleneck? This is where the real detective work began.

Installing Query Monitor

Query Monitor is a WordPress plugin that shows every database query executed on each page load — how long each takes, where it’s called from, and which ones are duplicated.

# Install via WP-CLI
wp plugin install query-monitor --activate

# Or: Plugins → Add New → Search "Query Monitor"

No configuration needed. An admin toolbar menu appears immediately showing database stats — total queries, total query time, PHP errors, and external HTTP requests. It’s essential for performance debugging.

First look: the numbers were shocking

We loaded the shop page with 2 filters active and checked Query Monitor:

Total Queries:    487
Total Query Time: 8.23 seconds
Slowest Query:    4.82 seconds
Duplicate Queries: 236

A well-optimised WooCommerce site should have 50–100 queries per page, under 1 second total query time, and fewer than 10 duplicates. We were 8x over on both query count and time.

The slowest query: the 4x meta join nightmare

The single slowest query was taking 4.82 seconds and running on every product page load. The culprit was the WC Global Product Order plugin, which joined the postmeta table four times in a single query:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
LEFT JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
LEFT JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id)
LEFT JOIN wp_postmeta AS mt3 ON (wp_posts.ID = mt3.post_id)
JOIN wp_icl_translations wpml_translations
    ON wp_posts.ID = wpml_translations.element_id
WHERE wp_postmeta.meta_key = '_wc_gpo_custom_order'
  AND mt1.meta_key = '_wc_gpo_custom_order'
  AND mt2.meta_key = '_wc_gpo_custom_order'
  AND mt3.meta_key = '_wc_gpo_custom_order'
  AND wp_posts.post_type = 'product'
ORDER BY CAST(COALESCE(wp_postmeta.meta_value, '0') AS SIGNED) DESC
LIMIT 0, 24;

Why this query is catastrophic

The duplicate query offenders

1. User meta queries — 194 duplicates

This exact query ran 194 times on a single page load — WordPress and WooCommerce each loading user meta independently instead of sharing the cached result:

SELECT meta_key, meta_value
FROM wp_usermeta
WHERE user_id = 1

2. WPML string translation — 42 duplicates

WPML loaded each translation string individually instead of batch-loading. The same query for WooCommerce strings like “Add to cart” and “Price” ran 42 times per page.

3. Product meta lookups — 38 duplicates

38 separate requests to look up the same product’s price, because different plugins and theme functions all independently queried it without checking if it was already loaded.

Missing database indexes

Think of a database index like a book index. Without it: to find all records with meta_key = '_price', MySQL reads every single row. With it: MySQL jumps directly to the relevant rows.

We checked the existing indexes:

SHOW INDEX FROM wp_postmeta;
-- Result: Only 1 index (primary key)

We added the critical missing indexes:

-- Index on meta_key (most queried column)
ALTER TABLE wp_postmeta
ADD INDEX idx_meta_key (meta_key(191));

-- Composite index on post_id + meta_key
ALTER TABLE wp_postmeta
ADD INDEX idx_post_meta (post_id, meta_key(191));

-- WPML translation indexes
ALTER TABLE wp_icl_translations
ADD INDEX idx_element_id (element_id);

ALTER TABLE wp_icl_translations
ADD INDEX idx_element_language (element_id, element_type(50), language_code(10));

The WPML performance cost

WPML adds multilingual support but wraps every query in translation logic. A simple WooCommerce product query becomes a nested subquery joining multiple translation tables — adding significant overhead to every single database call site-wide.

This isn’t a flaw in WPML specifically — it’s the fundamental cost of multilingual support. But it means every other optimisation has to work harder to compensate, and indexes on the WPML tables become critical.

Key findings from this phase