Slow WordPress Queries

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;

Leave a Comment