Index WP MySQL For Speed DDL – Antelope

by

This is the data definition language used by the Index MySQL For Speed plugin to add and remove high-performance keys. This is the code used for older versions of MariaDB and MySQL, the ones supporting the Antelope version of the InnoDB storage engine.

Notice that each table gets just one ALTER TABLE statement. This allows MariaDB or MySQL to handle the table just once. It’s faster. And, some managed service provider environments don’t allow tables without primary keys: dropping them and then adding them in one ALTER TABLE statement meets that requirement.

Adding High-Performance Keys

ALTER TABLE wp_commentmeta
  ADD UNIQUE KEY meta_id (meta_id),
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (comment_id, meta_id),
  DROP KEY comment_id,
  ADD KEY comment_id (comment_id, meta_key(32)),
  DROP KEY meta_key,
  ADD KEY meta_key (meta_key(32), meta_value(32)),
  ADD KEY meta_value (meta_value(32), meta_key(32));

ALTER TABLE wp_comments
  ADD UNIQUE KEY comment_ID (comment_ID),
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (comment_post_ID, comment_ID),
  DROP KEY comment_approved_date_gmt,
  ADD KEY comment_approved_date_gmt (comment_approved, comment_date_gmt, comment_ID),
  DROP KEY comment_date_gmt,
  ADD KEY comment_date_gmt (comment_date_gmt, comment_ID),
  DROP KEY comment_parent,
  ADD KEY comment_parent (comment_parent, comment_ID),
  DROP KEY comment_author_email,
  ADD KEY comment_author_email (comment_author_email, comment_post_ID, comment_ID),
  ADD KEY comment_post_parent_approved (
                        comment_post_ID, comment_parent,
                        comment_approved, comment_type, user_id,
                        comment_date_gmt, comment_ID),
  DROP KEY comment_post_ID;

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

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

ALTER TABLE wp_posts
  DROP KEY post_name,
  ADD KEY post_name (post_name(32)),
  DROP KEY post_parent,
  ADD KEY post_parent (post_parent, post_type, post_status),
  DROP KEY type_status_date,
  ADD KEY type_status_date (post_type, post_status, post_date, post_author),
  DROP KEY post_author,
  ADD KEY post_author (post_author, post_type, post_status, post_date);

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

ALTER TABLE wp_usermeta
  ADD UNIQUE KEY umeta_id (umeta_id),
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (user_id, umeta_id),
  DROP KEY user_id,
  ADD KEY user_id (user_id, meta_key(32), meta_value(32), umeta_id),
  DROP KEY meta_key,
  ADD KEY meta_key (meta_key(32), meta_value(32), umeta_id),
  ADD KEY meta_value (meta_value(32), umeta_id);
ALTER TABLE wp_users
  ADD KEY display_name (display_name(32));

Reverting to WordPress Standard Keys

ALTER TABLE wp_commentmeta
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (meta_id),
  DROP KEY comment_id,
  ADD KEY comment_id (comment_id),
  ADD KEY meta_key (meta_key(191)),
  DROP KEY meta_id,
  DROP KEY meta_value;
  
ALTER TABLE wp_comments
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (comment_ID),
  ADD KEY comment_post_ID (comment_post_ID),
  DROP KEY comment_approved_date_gmt,
  ADD KEY comment_approved_date_gmt (comment_approved, comment_date_gmt),
  DROP KEY comment_date_gmt,
  ADD KEY comment_date_gmt (comment_date_gmt),
  DROP KEY comment_parent,
  ADD KEY comment_parent (comment_parent),
  DROP KEY comment_author_email,
  ADD KEY comment_author_email (comment_author_email(10)),
  DROP KEY comment_ID,
  DROP KEY comment_post_parent_approved;
  
ALTER TABLE wp_options
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (option_id),
  ADD UNIQUE KEY option_name (option_name),
  DROP KEY option_id;
  
ALTER TABLE wp_postmeta
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (meta_id),
  ADD KEY post_id (post_id),
  DROP KEY meta_key,
  ADD KEY meta_key (meta_key(191)),
  DROP KEY meta_id,
  DROP KEY meta_value;
  
ALTER TABLE wp_posts
  DROP KEY post_name,
  ADD KEY post_name (post_name(191)),
  DROP KEY post_parent,
  ADD KEY post_parent (post_parent),
  DROP KEY type_status_date,
  ADD KEY type_status_date (post_type, post_status, post_date, ID),
  DROP KEY post_author,
  ADD KEY post_author (post_author);
  
ALTER TABLE wp_termmeta
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (meta_id),
  ADD KEY term_id (term_id),
  DROP KEY meta_key,
  ADD KEY meta_key (meta_key(191)),
  DROP KEY meta_id,
  DROP KEY meta_value;
  
ALTER TABLE wp_usermeta
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (umeta_id),
  ADD KEY user_id (user_id),
  DROP KEY meta_key,
  ADD KEY meta_key (meta_key(191)),
  DROP KEY umeta_id,
  DROP KEY meta_value;

ALTER TABLE wp_users
  DROP KEY display_name;

2 thoughts on “Index WP MySQL For Speed DDL – Antelope”

Leave a Comment