Adding high-performance keys to MySQL tables to speed up WordPress 🧑🏿‍🚀

wp_postmeta table should be the biggest table in your database, if not, there is a problem. Read wp_actionscheduler_actions & wp_actionscheduler_logs tables

When you install WordPress for the first time, tables are created in the database, for example for the wp_postmeta table:

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 problem?

• AUTO_INCREMENT slows down most queries, because of having to look in the secondary index to find auto_inc ID, then looking in data for the actual ID you need.
• BIGINT and AUTO_INCREMENT are overkill
• meta_key or meta_value will never be NULL 😀

solution:

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;

On an existing tables, We can add high-performance keys that match the most common WordPress queries:

wp_postmeta

ALTER TABLE wp_postmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_postmeta DROP PRIMARY KEY;
ALTER TABLE wp_postmeta ADD PRIMARY KEY (post_id, meta_key, meta_id);
ALTER TABLE wp_postmeta DROP KEY post_id;
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key, post_id);

This will create new high-performance keys:

We can do the same for other major tables:

wp_posts

ALTER TABLE wp_posts DROP KEY type_status_date;
ALTER TABLE wp_posts ADD KEY type_status_date (post_type, post_status, post_date, post_author, ID);
ALTER TABLE wp_posts DROP KEY post_author;
ALTER TABLE wp_posts ADD KEY post_author (post_author, post_type, post_status, post_date, ID);

wp_options

ALTER TABLE wp_options ADD UNIQUE KEY option_id (option_id);
ALTER TABLE wp_options DROP PRIMARY KEY;
ALTER TABLE wp_options ADD PRIMARY KEY (autoload, option_id);
ALTER TABLE wp_options DROP KEY autoload;

wp_usermeta

ALTER TABLE wp_usermeta ADD UNIQUE KEY umeta_id (umeta_id);
ALTER TABLE wp_usermeta DROP PRIMARY KEY;
ALTER TABLE wp_usermeta ADD PRIMARY KEY (user_id, meta_key, umeta_id);
ALTER TABLE wp_usermeta DROP KEY user_id;
ALTER TABLE wp_usermeta DROP KEY meta_key;
ALTER TABLE wp_usermeta ADD KEY meta_key (meta_key, user_id);

wp_termmeta

ALTER TABLE wp_termmeta ADD UNIQUE KEY meta_id (meta_id);
ALTER TABLE wp_termmeta DROP PRIMARY KEY;
ALTER TABLE wp_termmeta ADD PRIMARY KEY (term_id, meta_key, meta_id);
ALTER TABLE wp_termmeta DROP KEY term_id;
ALTER TABLE wp_termmeta DROP KEY meta_key;
ALTER TABLE wp_termmeta ADD KEY meta_key (meta_key, term_id);

The schema changes mentioned here are automated in Index WP MySQL For Speed WordPress plugin.

Source: MySQL Documents by Rick James
Speeding up WordPress database operations by Oliver Jones