simple sql query on wp_postmeta very slow

Question

I have a wordpress installation and I need this query on every post:

select post_id, meta_key from wp_postmeta where meta_key = 'mykey' and meta_value = 'somevalue'

I have 3M rows on that table, and the query takes about 6 sec to complete. I think it should be much faster. If I show the index of the table, it returns me:

SHOW INDEX FROM wp_postmeta

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment   
wp_postmeta 0   PRIMARY 1   meta_id A   3437260 NULL    NULL        BTREE       
wp_postmeta 1   post_id 1   post_id A   1718630 NULL    NULL        BTREE       
wp_postmeta 1   meta_key    1   meta_key    A   29  191 NULL    YES BTREE   

If I make a explain, it returns me this:

explain select post_id, meta_key from wp_postmeta where meta_key = 'mykey' and meta_value = 'somevalue'


id  select_type table   type    possible_keys   key key_len ref rows    Extra   
1   SIMPLE  wp_postmeta ref meta_key    meta_key    767 const   597392  Using where

I'm not very good in mysql so I don't know how to check or solve it. Can you give me some orientation on where the problem is??

Thanks you all.

Answer 1

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

Answer 2

If you are looking for DB performance "out of the box", then the post meta table is the wrong place to store things that you will want to search for. The way you describe your query it will be much better to use a taxonomy for that usage case.

But if you are already too much "down the rabbit hole" to restructure your DB, you should look into caching solution, which might not help with speeding up the query, but with good caching you will suffer the delay only once. Use cron to generate the cache when needed, and there will be zero noticeable user impact.