simple sql query on wp_postmeta very slow

Question

wp_postmeta has inefficient indexes. The published table (see Wikipedia) is

CREATE TABLE wp_postmeta (
  meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  post_id bigint(20) unsigned NOT NULL DEFAULT '0',
  meta_key varchar(255) DEFAULT NULL,
  meta_value longtext,
  PRIMARY KEY (meta_id),
  KEY post_id (post_id),
  KEY meta_key (meta_key)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

The problems:

The AUTO_INCREMENT provides no benefit; in fact it slows down most queries (because of having to look in secondary index to find auto_inc id, then looking in data for actual id you need)
The AUTO_INCREMENT is extra clutter – both on disk and in cache.
Much better is PRIMARY KEY(post_id, meta_key) — clustered, handles both parts of usual JOIN.
BIGINT is overkill, but that can’t be fixed without changing other tables.
VARCHAR(255) can be a problem in MySQL 5.6 with utf8mb4; see workarounds below.
When would meta_key or meta_value ever be NULL?

The solutions:

CREATE TABLE wp_postmeta (
    post_id BIGINT UNSIGNED NOT NULL,
    meta_key VARCHAR(255) NOT NULL,
    meta_value LONGTEXT NOT NULL,
    PRIMARY KEY(post_id, meta_key),
    INDEX(meta_key)
    ) ENGINE=InnoDB;

The typical usage:

JOIN wp_postmeta AS m  ON p.id = m.post_id
WHERE m.meta_key = '...'

Notes:

The composite PRIMARY KEY goes straight to the desired row, no digression through secondary index, nor search through multiple rows.
INDEX(meta_key) may or may not be useful, depending on what other queries you have.
InnoDB is required for the ‘clustering’.
Going forward, use utf8mb4, not utf8. However, you should be consistent across all WP tables and in your connection parameters.

The error “max key length is 767”, which can happen in MySQL 5.6 when trying to use CHARACTER SET utf8mb4. Do one of the following (each has a drawback) to avoid the error:

Upgrade to 5.7.7 for 3072 byte limit — your cloud may not provide this;
Change 255 to 191 on the VARCHAR — you lose any keys longer than 191 characters (unlikely?);
ALTER .. CONVERT TO utf8 — you lose Emoji and some of Chinese;
Use a “prefix” index — you lose some of the performance benefits;
Reconfigure (if staying with 5.6.3 – 5.7.6) — 4 things to change: Barracuda + innodb_file_per_table + innodb_large_prefix + dynamic or compressed.

Potential incompatibilities

meta_id is probably not used anywhere. (But it is a risk to remove it).
You could keep meta_id and get most of the benefits by changing to these indexes: PRIMARY KEY(post_id, meta_key, meta_id), INDEX(meta_id), INDEX(meta_key, post_id). (Note: By having meta_id on the end of the PK, it is possible for post_id+meta_key to be non-unique.)
Changing from BIGINT to a smaller datatype would involve changing other tables, too.

utf8mb4

Moving to 5.7 should not be incompatible.
Shrinking to VARCHAR(191) would require the user to understand that the limit is now the arbitrary “191” instead of the previous arbitrary limit of “255”.
The ‘reconfigure’ fix is DBA issues, not incompatibility issues.

Comment

I hope that some of what I advocate is on the WordPress roadmap. Meanwhile, stackoverflow and dba.stackexchange are cluttered with “why is WP running so slow”. I believe that the fixes given here would cut back significantly in such complaint-type Questions.

Note that some users are changing to utf8mb4 in spite of compatibility issues. Then they get in trouble. I have tried to address all the MySQL issues they are having.

Taken from Rick James mysql blog: source

Related Posts:

Leave a Comment Cancel reply