WordPress.org

Make WordPress Core

Opened 8 years ago

Closed 8 years ago

Last modified 8 years ago

#3170 closed defect (bug) (fixed)

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

Reported by: BrianLayman Owned by:
Milestone: Priority: high
Severity: major Version: 2.0
Component: General Keywords: Database index
Focuses: Cc:

Description

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.

Change History (7)

comment:1 @intoxination8 years ago

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

comment:2 @ryan8 years ago

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:3 @foolswisdom8 years ago

Confirmed that this is not relevant in trunk.

comment:4 @BrianLayman8 years ago

  • Resolution fixed deleted
  • Severity changed from normal to major
  • Status changed from closed to reopened

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.

comment:5 @markjaquith8 years ago

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.

comment:6 @markjaquith8 years ago

  • Resolution set to fixed
  • Status changed from reopened to closed

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

comment:7 @anonymous8 years ago

  • Milestone 2.0.5 deleted

Milestone 2.0.5 deleted

Note: See TracTickets for help on using tickets.