Finding the Bottleneck: Database Query Analysis

Introduction

In Part 2 of this series, we set up comprehensive load testing with Grafana K6 and discovered shocking performance metrics:

23-second response times at 95th percentile
23% error rate under moderate load
Exponential degradation with concurrent users
Database connection pool maxing out at 150 connections

We had objective proof the site was broken, but we still didn’t know why. The server had plenty of CPU and RAM headroom, so the bottleneck wasn’t hardware.

In this post, we’ll cover:

Installing and using Query Monitor to analyse database queries
Discovering the “4x meta join” catastrophe
Finding 194 duplicate queries per page load
Identifying missing database indexes
The WPML translation query nightmare
Server resource monitoring during load tests

This is where the real detective work begins.

Installing Query Monitor

Query Monitor is a WordPress plugin that shows you exactly what queries run on each page load, how long they take, and where they’re called from.

Installation


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

# Or install manually from WordPress dashboard:
# Plugins → Add New → Search "Query Monitor"
```

**No configuration needed** - it just works. An admin toolbar menu appears showing database stats.

### What Query Monitor Shows

Once activated, you'll see in the admin bar:

* **Database queries:** 487 queries in 8.23s
* **PHP errors:** 12 warnings
* **HTTP requests:** 3 external API calls

Click on it to see a detailed breakdown of:
* Every SQL query executed
* Execution time for each query
* Where in the code it was called
* Duplicate queries
* Slow queries (over 0.05s)

**This is gold 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

Let that sink in:

487 database queries for a single page load
8.23 seconds just in database time (not counting PHP, rendering, etc.)
236 duplicate queries – the same query executed multiple times

For comparison: A well-optimised WooCommerce site should have:

50-100 queries per page
Under 1 second total query time
Less than 10 duplicates

We were 8x over the normal query count and 8x over normal query time.

The Slowest Query: 4x Meta Join Nightmare

Query Monitor highlighted the slowest query – it was taking 4.82 seconds and running on every product page:


[SQL]
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
    AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type)
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'
  AND wp_posts.post_status = 'publish'
  AND wpml_translations.language_code = 'en'
GROUP BY wp_posts.ID
ORDER BY CAST(COALESCE(wp_postmeta.meta_value, '0') AS SIGNED) DESC
LIMIT 0, 24;
```

### What's Wrong with This Query?

**Problem 1: Multiple JOINs on the Same Table**

The query joins `wp_postmeta` **four times** (wp_postmeta, mt1, mt2, mt3). Each JOIN multiplies the number of rows being compared.

**The math:**
* wp_posts: ~1,000 products
* wp_postmeta: ~3,000 rows per product (WooCommerce stores everything as meta)
* 4 JOINs = 1,000 × 3,000 × 3,000 × 3,000 × 3,000 = **81 trillion row comparisons**

**This is computational insanity.**

**Problem 2: SQL_CALC_FOUND_ROWS**

This forces MySQL to calculate the total number of matching rows even though we're only displaying 24 products. It prevents MySQL from using optimizations and indexes effectively.

**Problem 3: No Indexes**

The query searches for `meta_key = '_wc_gpo_custom_order'` but there's no index on the `meta_key` column, forcing a full table scan.

**Problem 4: WPML Translation Join**

Every product query has to join through WPML's translation table, adding another layer of complexity.

### Where Did This Come From?

Query Monitor showed the **call stack:**
```
WC_Global_Product_Order::pre_get_posts()
  → wp-content/plugins/woocommerce-global-product-order/includes/class-wc-global-product-order.php:247

The culprit: WC Global Product Order plugin.

This plugin allows you to manually order products, but it does so by joining the postmeta table 4 times on every product query. For 1,000 products, this is a performance killer.

Finding Duplicate Queries

Query Monitor has a “Duplicate Queries” tab that groups identical queries together.

The Top Offenders

1. User Meta Queries – 194 duplicates


[SQL]
SELECT meta_key, meta_value
FROM wp_usermeta
WHERE user_id = 1

This exact query ran 194 times on a single page load!

Why?

WordPress and WooCommerce individually load user meta for every function that needs it, instead of loading it once and caching it.

2. WPML String Translation – 42 duplicates


[SQL]
SELECT context, name, value
FROM wp_icl_strings
WHERE language = 'en'
AND context = 'woocommerce'

42 times for translating WooCommerce strings like “Add to cart”, “Price”, “Sale”, etc.

Why?

WPML loads translations for every string individually instead of batch-loading all strings at once.

3. Product Meta Lookups – 38 duplicates


[SQL]
SELECT post_id, meta_key, meta_value
FROM wp_postmeta
WHERE post_id = 123
AND meta_key = '_price'

38 times looking up the same product’s price!

Why?

Different plugins and theme functions all independently check product prices without checking if it’s already been loaded.

The Impact of Duplicates

Math time:

Average query time: 0.015 seconds
236 duplicate queries × 0.015s = 3.54 seconds wasted
That’s 43% of our total database time!

Eliminating duplicates alone could cut database time nearly in half.

Missing Database Indexes

Query Monitor also highlights queries that would benefit from indexes.

What Are Database Indexes?

Think of a database index like an index in a book:

Without an index: To find all mentions of “performance” in a 500-page book, you’d have to read every single page.

With an index: Jump directly to pages 45, 127, 203, 389 where “performance” appears.

Same concept in databases.

Critical Missing Indexes

We ran this query to check existing indexes:


[SQL]
SHOW INDEX FROM wp_postmeta;

Result: Only 1 index (on ).

Missing indexes we needed:


[SQL]
-- 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 (common query pattern)
ALTER TABLE wp_postmeta 
ADD INDEX idx_post_meta (post_id, meta_key(191));

-- Index on meta_value (for sorting by price, etc.)
ALTER TABLE wp_postmeta 
ADD INDEX idx_meta_value (meta_key(191), meta_value(50));

Why 191 characters?

MySQL has a limit on index key length. For UTF-8 character sets:

191 characters × 4 bytes = 764 bytes (under the 767-byte limit)
192 characters would exceed the limit and fail

Checking WPML Translation Indexes


[SQL]
SHOW INDEX FROM wp_icl_translations;

Result: Only 1 index (on primary key).

Missing indexes we needed:


[SQL]
-- Index on element_id (product ID)
ALTER TABLE wp_icl_translations 
ADD INDEX idx_element_id (element_id);

-- Composite index on element + language
ALTER TABLE wp_icl_translations 
ADD INDEX idx_element_language (element_id, element_type(50), language_code(10));

-- Index on translation relationship
ALTER TABLE wp_icl_translations 
ADD INDEX idx_trid_language (trid, language_code(10));

The Impact of Missing Indexes

We added just the index to test:


[SQL]
ALTER TABLE wp_postmeta ADD INDEX idx_meta_key (meta_key(191));

Before: Query took 4.82 seconds
After: Same query took 1.23 seconds

75% improvement from ONE index!

The WPML Translation Problem

WPML (WordPress Multilingual Plugin) adds translation support but comes with a serious performance cost.

How WPML Works

Every database query for posts, products, or pages gets wrapped in WPML translation logic:

Original WooCommerce query:


[SQL]
SELECT ID, post_title, post_content
FROM wp_posts
WHERE post_type = 'product'
AND post_status = 'publish'
LIMIT 24;

What WPML transforms it into:


[SQL]
SELECT wp_posts.ID, wp_posts.post_title, wp_posts.post_content
FROM wp_posts
JOIN wp_icl_translations t 
    ON wp_posts.ID = t.element_id
    AND t.element_type = 'post_product'
WHERE wp_posts.post_type = 'product'
AND wp_posts.post_status = 'publish'
AND t.language_code = 'en'
AND t.element_id IN (
    SELECT element_id FROM wp_icl_translations
    WHERE trid = (
        SELECT trid FROM wp_icl_translations
        WHERE element_id = wp_posts.ID
        AND element_type = 'post_product'
    )
    AND language_code = 'en'
)
LIMIT 24;

Notice the problem?

Added 2 JOINs and 2 subqueries
Query went from simple to complex
For 24 products × 1,000 products in catalog = massive overhead

WPML Query Count

On a single shop page:

Without WPML: 87 queries
With WPML: 312 queries

WPML added 225 queries just for translation support!

The String Translation Nightmare

WPML also translates every string (“Add to cart”, “Price”, “Out of stock”, etc.):


[SQL]
SELECT context, name, value, domain_name_context_md5
FROM wp_icl_strings s
LEFT JOIN wp_icl_string_translations st
    ON s.id = st.string_id
    AND st.language = 'en'
WHERE s.context = 'woocommerce'
AND s.name = 'add_to_cart_text'

This query runs for EVERY translatable string.

For a WooCommerce shop page:

~150 unique strings to translate
Each one triggers a separate query
150 additional database queries

Server Resource Monitoring

While analysing queries, we also monitored server resources during load tests to correlate database queries with system impact.

Monitoring Script


[BASH]
#!/bin/bash
# monitor-server.sh - Run during load tests

echo "Starting server monitoring..."
echo "Timestamp, Load, CPU%, Memory_Used, DB_Connections, PHP_Processes" > metrics.csv

while true; do
    TIMESTAMP=$(date +%s)
    LOAD=$(uptime | awk -F'load average:' '{print $2}' | cut -d, -f1 | xargs)
    CPU=$(top -bn1 | grep "Cpu(s)" | awk '{print $2}' | cut -d% -f1)
    MEM=$(free | grep Mem | awk '{printf("%.1f"), $3/$2 * 100.0}')
    DB_CONN=$(mysql -e "SHOW STATUS LIKE 'Threads_connected';" | tail -1 | awk '{print $2}')
    PHP_PROC=$(ps aux | grep php-fpm | grep -v grep | wc -l)
    
    echo "$TIMESTAMP, $LOAD, $CPU, $MEM, $DB_CONN, $PHP_PROC" >> metrics.csv
    
    sleep 5
done

Run this in a separate terminal:


[BASH]
chmod +x monitor-server.sh
./monitor-server.sh
```

### What We Discovered

**During K6 load test with 40 concurrent users:**
```
Time    Load  CPU%  Memory%  DB_Conn  PHP_Proc
14:30   1.2   28%   45%      45       12
14:31   2.8   35%   48%      89       18
14:32   4.5   42%   52%      127      24
14:33   6.2   38%   55%      150      28  ← Maxed out!
14:34   7.8   35%   58%      150      30
14:35   9.1   32%   61%      150      32  ← Still maxed

Key observations:

CPU stayed under 50% – Not the bottleneck
Memory stayed under 65% – Not the bottleneck
Database connections hit 150 – THE BOTTLENECK!
PHP-FPM processes kept growing – Because requests were queuing

The database connection pool was exhausted.

MySQL was configured with:


[SQL]
SHOW VARIABLES LIKE 'max_connections';
-- Result: 150

With 487 queries per page load and 40 concurrent users, we were trying to execute:

487 queries/page × 40 users = 19,480 queries simultaneously

No wonder the database was drowning!

Creating a Performance Report

We compiled all our findings into a diagnostic report:

Critical Issues Found

1. WC Global Product Order Plugin

Adds 4x postmeta JOIN to every product query
Single query takes 4.82 seconds
Impact: 75% of slowness on shop pages

2. Duplicate Queries

194 duplicate usermeta queries
42 duplicate WPML string translations
38 duplicate product meta queries
Impact: 3.54 seconds wasted per page (43% of DB time)

3. Missing Database Indexes

No index on
No index on
No composite indexes for common query patterns
Impact: Queries 10-100x slower than they should be

4. WPML Overhead

Adds 225 queries per page load
Nested subqueries on every product
No caching of translation strings
Impact: 3x increase in total queries

5. Database Connection Pool

Max connections: 150
Peak usage: 150 (100% utilized)
Requests queuing due to connection exhaustion
Impact: Timeouts under load

Query Analysis Tools Summary

Tools We Used

1. Query Monitor Plugin

Shows every query and execution time
Highlights duplicates and slow queries
Shows caller (which plugin/theme caused it)
Best for: Initial diagnosis

2. MySQL Slow Query Log

Enable slow query logging:


[SQL]
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;  -- Log queries over 0.5s
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
</sql></pre>


				
				
				
				
				<p>Then analyze:</p>

<pre><code class="language-sql">
[SQL]
# View slow query log
sudo tail -f /var/log/mysql/slow-query.log

# Or use mysqldumpslow for analysis
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Best for: Production monitoring

3. EXPLAIN for Query Analysis

For any slow query, use EXPLAIN to see the execution plan:


[SQL]
EXPLAIN SELECT wp_posts.ID
FROM wp_posts
LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
WHERE wp_postmeta.meta_key = '_price'
AND wp_posts.post_type = 'product';

Output shows: