Index WP MySQL For Speed, the WordPress plugin, allows users to monitor their MariaDB or MySQL database traffic and upload the results of those monitors. It gathers information about the queries, including how long they take and how many there are.
Analyzing the uploaded monitor data allows me to identify the most frequent queries and the slowest. Of course the sites uploading data are self-selected, so this is by no means a random sample.
This note, a work in progress, is an analysis of some of that information.
Top Three Queries
None of these are particularly surprising. They fetch options, post metadata, and post data.
SELECT option_value FROM wp_options WHERE option_name = ?s? LIMIT 1 -- avg 1.5ms
SELECT post_id, meta_key, meta_value -- avg 3.0ms
FROM wp_postmeta WHERE post_id IN (?i?) ORDER BY meta_id ASC
SELECT * FROM wp_posts WHERE ID = ?i? LIMIT 1 -- avg 4.13ms
There may be a tiny performance opportunity, to remove the ORDER BY clause, in the metadata query.
Taxonomy Queries
This query is the fourth most frequent in the sample set. Its average run time was a whopping 210ms (milliseconds).
SELECT DISTINCT t.term_id
FROM wp_terms AS t
LEFT JOIN wp_termmeta
ON ( t.term_id = wp_termmeta.term_id AND wp_termmeta.meta_key=?s?)
INNER JOIN wp_term_taxonomy AS tt
ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN (?s?)
AND tr.object_id IN (?i?)
AND ( ( wp_termmeta.meta_key = ?s? OR wp_termmeta.meta_key IS NULL ) )
ORDER BY wp_termmeta.meta_value+0 ASC, t.name ASC
(Note: in this presentation, ?i? stands for some integer, and ?s? for some text string.)
This related query is the sixth most frequent, with an average run time of 194ms.
SELECT DISTINCT t.term_id
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN (?s?)
AND tr.object_id IN (?i?)
ORDER BY t.name ASC
And this one is at number 24, with an average run time of 71 ms.
SELECT DISTINCT t.term_id, tr.object_id
FROM wp_terms AS t
INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships AS tr
ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN (?s?, ?s?, ?s?)
AND tr.object_id IN (?i?)
ORDER BY t.name ASC
It’s clear WordPress’s taxonomy features load the WordPress database more than other features.
Analysis
On a test WooCommerce site this query
ANALYZE FORMAT=JSON
SELECT DISTINCT t.term_id
FROM wp_terms AS t
LEFT JOIN wp_termmeta
ON ( t.term_id = wp_termmeta.term_id AND wp_termmeta.meta_key='order_pa_writer')
INNER JOIN wp_term_taxonomy AS tt
ON t.term_id = tt.term_id
INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE tt.taxonomy IN ('pa_distributor')
AND tr.object_id IN (129)
AND ( ( wp_termmeta.meta_key = 'order_pa_writer' OR wp_termmeta.meta_key IS NULL ) )
ORDER BY wp_termmeta.meta_value+0 ASC, t.name ASC
yields this plan explanation.
{
"query_block": {
"select_id": 1,
"r_loops": 1,
"r_total_time_ms": 0.082534193,
"filesort": {
"sort_key": "wp_termmeta.meta_value + 0, t.`name`",
"r_loops": 1,
"r_total_time_ms": 0.005265147,
"r_used_priority_queue": false,
"r_output_rows": 1,
"r_buffer_size": "12Kb",
"r_sort_mode": "packed_sort_key,rowid",
"temporary_table": {
"table": {
"table_name": "tr",
"access_type": "ref",
"possible_keys": ["PRIMARY", "term_taxonomy_id"],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["object_id"],
"ref": ["const"],
"r_loops": 1,
"rows": 10,
"r_rows": 10,
"r_table_time_ms": 0.014489202,
"r_other_time_ms": 0.007375225,
"filtered": 100,
"r_filtered": 100,
"using_index": true
},
"table": {
"table_name": "tt",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY", "term_id_taxonomy", "taxonomy"],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["term_taxonomy_id"],
"ref": ["horas.tr.term_taxonomy_id"],
"r_loops": 10,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.018196911,
"r_other_time_ms": 0.004039292,
"filtered": 13.66197205,
"r_filtered": 10,
"attached_condition": "tt.taxonomy = 'pa_distributor'"
},
"table": {
"table_name": "t",
"access_type": "eq_ref",
"possible_keys": ["PRIMARY"],
"key": "PRIMARY",
"key_length": "8",
"used_key_parts": ["term_id"],
"ref": ["horas.tt.term_id"],
"r_loops": 1,
"rows": 1,
"r_rows": 1,
"r_table_time_ms": 0.004270396,
"r_other_time_ms": 0.001276095,
"filtered": 100,
"r_filtered": 100
},
"table": {
"table_name": "wp_termmeta",
"access_type": "ref",
"possible_keys": ["PRIMARY", "meta_key"],
"key": "PRIMARY",
"key_length": "1030",
"used_key_parts": ["term_id", "meta_key"],
"ref": ["horas.tt.term_id", "const"],
"r_loops": 1,
"rows": 1,
"r_rows": 0,
"r_table_time_ms": 0.00379814,
"r_other_time_ms": 0.009465207,
"filtered": 100,
"r_filtered": 100,
"attached_condition": "trigcond(wp_termmeta.meta_key = 'order_pa_writer' or wp_termmeta.meta_key is null) and trigcond(wp_termmeta.meta_key = 'order_pa_writer')"
}
}
}
}
}
The table definitions are these. They’re all unaltered from the WordPress standard database definition.
CREATE TABLE wp_terms ( -- about 3100 rows
term_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(200) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
slug VARCHAR(200) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
term_group BIGINT NOT NULL DEFAULT '0',
PRIMARY KEY (term_id) USING BTREE,
INDEX slug (slug(191)) USING BTREE,
INDEX name (name(191)) USING BTREE
) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;
CREATE TABLE wp_term_taxonomy ( -- about 3100 rows, the same number as wp_terms
term_taxonomy_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
term_id BIGINT UNSIGNED NOT NULL DEFAULT '0',
taxonomy VARCHAR(32) NOT NULL DEFAULT '' COLLATE 'utf8mb4_unicode_ci',
description LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
parent BIGINT UNSIGNED NOT NULL DEFAULT '0',
count BIGINT NOT NULL DEFAULT '0',
PRIMARY KEY (term_taxonomy_id) USING BTREE,
UNIQUE INDEX term_id_taxonomy (term_id, taxonomy) USING BTREE,
INDEX taxonomy (taxonomy) USING BTREE
) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;
CREATE TABLE wp_term_relationships` ( -- modified junction table, about 11,300 rows
object_id BIGINT UNSIGNED NOT NULL DEFAULT '0',
term_taxonomy_id BIGINT UNSIGNED NOT NULL DEFAULT '0',
term_order INT NOT NULL DEFAULT '0',
PRIMARY KEY (object_id, term_taxonomy_id) USING BTREE,
INDEX term_taxonomy_id (term_taxonomy_id) USING BTREE
) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB;
Hello
did find any patch or hook to optimize that query
No, sorry to say, I haven’t been able to tighten up that query, But I believe its results are stored in a persistent object cache, so if you try one of those you’ll avoid repeating the slow query.
https://developer.wordpress.org/reference/classes/wp_object_cache/#persistent-cache-plugins