1

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.)

Improve this question
6
  • If this isn't an appropriate question for wpse, please let me know and I'll delete it.
    – O. Jones
    Apr 28, 2021 at 13:41
  • 3
    Keep in mind this isn't a forum so you need to word the question so that it's specific and you can mark an answer as not just the most useful response, but as the correct factual answer for all people with this question for all time
    – Tom J Nowell ♦
    Apr 28, 2021 at 14:03
  • This is a bit tricky because it the most cumbersome table will usually depend on what the site is being used like. Interesting project though. Do you have a GitHub for it so I can follow along? I do see the benefit of what you're trying to do.
    – Tony Djukic
    Apr 28, 2021 at 15:48
  • I'm aware of the audacity (foolishness ???) of trying to make software that claims to optimizae millions of sites. :-) I'm shooting for stuff that will help at least some sites first time around ... No github yet, but I will let you know. Thanks for your interest.
    – O. Jones
    Apr 28, 2021 at 16:36
  • Interesting. How would "appropriate indexes for the WP's MySQL tables to improve performance" look like?
    – Álvaro Franz
    Apr 30, 2021 at 16:10

1 Answer 1

Reset to default
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);
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.