6

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.

2 Answers 2

4

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

12
  • sorry but changing core table structure, especially in a non backward compatible way is just a bad general idea. Sep 25, 2017 at 4:52
  • @MarkKaplun - Most of what I suggest is backward compatible. And even if you can't make all the changes, you can get improvements with composite indexes and avoiding index prefixing (that some people use to deal with utf8mb4 in 5.6).
    – Rick James
    Sep 25, 2017 at 4:58
  • 1
    changing DB structure is the same as changing core code files. You are always under the "threat" of an upgrade reverting your changes. Removing columns sounds like a bad idea as it will most likely be added back, Sep 25, 2017 at 5:06
  • 1
    you understanding of mysql is probably at least one level above mine but the meta API supports multiple usage of the same key name per post. The issue of confusing between such rows is probably solved (or can be solved) by the use of the ID which you want to remove. It might be that the code do not actually use it, I am just very hesitant to assume it will never do that, or that no plugin does it. Sep 25, 2017 at 5:23
  • 3
    @RickJames and I have made a WordPress plugin to do this reindexing work. wordpress.org/plugins/index-wp-mysql-for-speed We detect all sorts of stuff like the presence of the Barrucuda version of the InnoDB storage engine, and other MySQL arcana, and do the right thing.
    – O. Jones
    Jun 14, 2021 at 17:10
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.