Query Profiling Tools

Query profiling is how you stop guessing. Use these tools to identify which queries and plugins are slow, confirm the execution plan (EXPLAIN), and validate improvements after indexing, caching, or code changes.

Query Profiling Tools

Tool	Scope	Usage	Strength	Limitation
Query Monitor (Plugin)	WordPress	Shows queries by plugin/theme, hook, and load time	Easy to use, per-page profiling	Overhead in production
WP-CLI + --explain	WordPress CLI	Analyzes specific queries from logs	Lightweight, CLI-based	Requires SQL knowledge
MySQL Slow Query Log	Database	Logs queries exceeding time threshold	Accurate DB-level profiling	Needs root access
EXPLAIN (SQL Command)	Database	Shows query execution plan	Pinpoints missing indexes	Technical, raw output
New Relic / Tideways	Application monitoring	Tracks queries, PHP functions, transactions	Continuous monitoring	Paid services

How Query Profiling Works

Step	Without Profiling	With Profiling	Benefit
Troubleshooting	Guesswork, trial-and-error	Identifies exact slow query	Saves time
WooCommerce Checkout	Slow during load spikes	Detects unindexed meta queries	Add an index; stabilize checkout
Search Queries	Heavy LIKE queries	Shows full table scans	Implement ElasticSearch/OpenSearch

Implementation Steps

1. Enable MySQL Slow Query Log:

   edit-mysql-config.sh

   sudo nano /etc/mysql/my.cnf

   Add:

   mysql-slow-query-log.cnf

   slow_query_log = 1
   slow_query_log_file = /var/log/mysql-slow.log
   long_query_time = 1

   Restart:

   restart-mysql.sh

   sudo systemctl restart mysql

2. Use EXPLAIN on Queries:

   explain-sample-query.sql

   EXPLAIN SELECT * FROM wp_postmeta WHERE meta_key = '_price';

3. Install Query Monitor Plugin (staging only).

4. Use WP-CLI to Profile Queries:

   wp-cli-explain-query.sh

   wp db query "EXPLAIN SELECT * FROM wp_posts WHERE post_type='product';"

5. Optional: Integrate New Relic / Tideways for continuous monitoring.

Static vs Dynamic Strategy

Site Type	Profiling Focus	Why
Static (Blogs)	Rarely needed except for plugin bloat	Content mostly cached
Dynamic (WooCommerce, LMS, Membership)	Continuous profiling with slow query log + Query Monitor	High volume of dynamic queries under load

Best Practices

1. Always profile on staging or during low-traffic windows.
2. Keep long_query_time at 1 second (lower to 0.5s for stricter tuning).
3. Use EXPLAIN to identify missing indexes before altering schema.
4. Pair query profiling with Redis object cache to verify impact.
5. Regularly rotate and archive slow query logs.

Verification Steps

Check slow query log exists:

query-profilling-tools-example-1.sh

ls -lh /var/log/mysql-slow.log

Expected Output:

query-profilling-tools-example-2.txt

-rw-r----- 1 mysql adm 12K Sep 21 12:00 /var/log/mysql-slow.log

Check EXPLAIN output:

query-profilling-tools-example-3.sql

EXPLAIN SELECT * FROM wp_posts WHERE post_type='product';

Expected Output (simplified):

explain-output.txt

id | select_type | table    | type | possible_keys | key     | rows | Extra
1  | SIMPLE      | wp_posts | ref  | type_status   | type    | 200  | Using where

Quick Lab

1. Enable MySQL slow query log.
2. Perform a WooCommerce checkout with 20 products.
3. Open /var/log/mysql-slow.log and copy the slowest query.
4. Run EXPLAIN on the query to check execution plan.
5. Add an index to optimize, then rerun profiling.
6. Measure TTFB again.

Cheat Sheet (Query Profiling)

Task	Command/Tool
Enable Slow Query Log	slow_query_log = 1 in my.cnf
Location	/var/log/mysql-slow.log
Run EXPLAIN	EXPLAIN SELECT ...;
WP-CLI Query	wp db query "EXPLAIN ..."
Check Log Size	ls -lh /var/log/mysql-slow.log
Continuous Profiling	New Relic, Tideways

Expected Outcome

With query profiling in place, you gain visibility into database bottlenecks so you can apply precise fixes (indexes, query changes, caching) and prove the impact.

What's Next

• Redis Persistent Object Cache
• Backend Query Optimization
• Backend Cleanup