mysqlwordpressperformanceindexingentity-attribute-value

Why are references to wp_postmeta so slow?


Fetching of attributes in WordPress (using MySQL) seems to be slower than necessary.

(This is a self-answered question, so proceed to my answer.)


Solution

  • The standard schema for wp_postmeta provides poor indexes. This leads to performance problems.

    By changing the schema to this, most references to meta data will be faster:

    CREATE TABLE wp_postmeta (
        post_id …,
        meta_key …,
        meta_value …,
        PRIMARY KEY(post_id, meta_key),
        INDEX(meta_key)
    ) ENGINE=InnoDB;
    

    Notes:

    If you would like to present your CREATE TABLE, I can provide an ALTER to convert it to this.

    If you need the ability to have multiple meta keys with the same key name for one post, then use this solution. It is nearly as good as the above suggestion.

        meta_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  -- keep after all
        ...
        PRIMARY KEY(post_id, meta_key, meta_id),  -- to allow dup meta_key for a post
    

    Source doc

    Possible ALTER

    Caveats:

    The SQL:

    ALTER TABLE wp_postmeta
        DROP PRIMARY KEY,
        DROP INDEX post_id,
        ADD PRIMARY KEY(post_id, meta_key, meta_id),  -- to allow dup meta_key for a post
        ADD INDEX(meta_id);    -- to keep AUTO_INCREMENT happy
    

    Plugin WP Index Improvements -- to do that alter, plus others. (As mentioned in the Comments, below.)