I’m designing a plugin to create database indexes. Suggestions?

I’m starting a (totally open-source) little project to create a WordPress plugin that will add appropriate indexes to WP’s MySQL tables to improve performance. (Some wp tables, notably wp_postmeta and wp_usermeta, can use better indexing.)

My goal: make larger sites (with more posts, users, and so forth) perform better.

What is the worst-performing database table in WordPress? And, what makes it perform badly?

Please answer this question with any feedback.

If you know of some other project that makes mine unnecessary, let me know about that too please.

(I have enough development, testing, and database skills to know what I’m getting into with this project.)

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

This plugin, Index WP MySQL for Speed, is released. GPLed source code is here.

Anecdotal experience shows speedups on some big WP and WooCommerce sites.

Here’s the data definition language it runs to create useful compound indexes.

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);
 
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);
 
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);
 
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;
 
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);
 
ALTER TABLE wp_comments ADD KEY comment_post_parent_approved
                     (comment_post_ID, comment_parent, comment_approved, comment_ID);

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0