Database Β» Adding high-performance keys to MySQL tables to speed up WordPress πŸ§‘πŸΏβ€πŸš€

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:


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:

TableStandard WP keysNew keys
wp_postmetameta_id: primary key
post_id
meta_key
post_id, meta_key, meta_id: primary key
meta_id: unique key
meta_key, post_id

We can do the same for other major tables:

See also  How to Reset WordPress Password from phpMyAdmin Database

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);
TableStandard WP keysNew keys
wp_postspost_type, post_status, post_date,ID
post_author
post_type,post_status,post_date,post_author,ID
post_author,post_type,post_status,post_date,ID

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;
TableStandard WP keysNew keys
wp_optionsoption_id: primary key
option_name: unique key
autoload
autoload, option_id: primary key
option_name: unique_key
option_id: unique key

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);
TableStandard WP keysNew keys
wp_usermetaumeta_id: primary key
user_id
meta_key
user_id, meta_key, umeta_id: primary key
umeta_id: unique key
meta_key, user_id

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);
TableStandard WP keysNew keys
wp_termmeta
meta_id: primary key
term_id
meta_key
term_id, meta_key, meta_id: primary key
meta_id: unique 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

Was this post helpful?

Leave a Comment

I enjoy constructive responses and professional comments to my posts, and invite anyone to comment or link to my site.

Recommended