WordPress.org

Make WordPress Core

Opened 10 years ago

Closed 10 years ago

Last modified 10 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)

#1 @intoxination
10 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.

#2 @ryan
10 years ago

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

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

#3 @foolswisdom
10 years ago

Confirmed that this is not relevant in trunk.

#4 @BrianLayman
10 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.

#5 @markjaquith
10 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.

#6 @markjaquith
10 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

#7 @anonymous
10 years ago

  • Milestone 2.0.5 deleted

Milestone 2.0.5 deleted

Note: See TracTickets for help on using tickets.