Sites installed with 2.0 and newer do not have the post_status index. This slows WP sites.

[BrianLayman]

If you create a site from the 2.04 install and it only has the
PRIMARY and post_name indices/indexes in the posts table.

According to upgrade-schema.php, you are only suppose to have KEY and
post_name:
CREATE TABLE $wpdb->posts (

[A BUNCH OF FIELDS]
PRIMARY KEY (ID),
KEY post_name (post_name)

);

However, in upgrade-functions.php, the 'post_status' index is added as
part of function upgrade_101().

Clean up indices, add a few
add_clean_index($wpdb->posts, 'post_name');
add_clean_index($wpdb->posts, 'post_status');
add_clean_index($wpdb->categories, 'category_nicename');
add_clean_index($wpdb->comments, 'comment_approved');
add_clean_index($wpdb->comments, 'comment_post_ID');
add_clean_index($wpdb->links , 'link_category');
add_clean_index($wpdb->links , 'link_visible');

It seems upgrade_101 is not called when a new DB is create because the
version is already 3440.

All of those other indices have been moved over to the schema file but
post_status has not.

[intoxination]

+1 for this. I have gone through and manually added the post_status index on a couple of sites with 10,000+ posts and it made a very noticeable performance increase.

[ryan]

(In [4255]) Add post_status key. fixes #3170

[foolswisdom]

Confirmed that this is not relevant in trunk.

[BrianLayman]

Reopening because the attached dif does nothing to fix the sites that were created without the index. Please close if the code was attached/changed elsewhere.

There needs to be an upgrade function in 2.0.5 that adds this or all of the newer sites will continue to suffer from the resultant slowdown until they upgrade to 2.1 and the whole thing is replaced.

[markjaquith]

Brian,

Good call. We have a function that automatically upgrades the database to the schema, but the database version number needs to be incremented for that to be triggered. Fixing now.

[markjaquith]

(In [4428]) Database bump needed to add the post_status key for existing installs. Props Brian Layman. fixes #3170

[(none)]

Milestone 2.0.5 deleted