[wp-hackers] 2.0.4 Performance Issues

[Brian Layman]

>
http://trac.wordpress.org/browser/tags/2.0.4/wp-includes/classes.php#L37
9
There's actually an issue in get_all_page_ids(), right at line 379.
posts.pages_status is not indexed.  So, the DB has to recall and examine
every post record each time it is called.  That's GOT to take a long
time on older blogs.

Paul, I'd be curious to find out how much improvement you see after
running this statement on your DB:
ALTER TABLE `YOURPREFIX_posts` ADD INDEX `post_status` ( `post_status` )


Adding that index should speed up RSS feeds, pings, the post page,
inline uploading, Previous and next post navigation, and several other
places as well.  It should radicaly reduce the load on the DB too.
Post_status is used in enough where clauses that it SHOULD have a
non-unique index on it.  It is used sometimes with ID but I don't know
enough about mysql's query engine's inner workings to know if a joint
index would perform any better than two separate indexes.  

post_author isn't used in as nearly as many places, mostly in the admin
pages as I see it, but it might be worth adding that too.  It would
speed up getting the get_posts call when getting the list of posts the
user is allowed to edit.

Has anyone done an analysis of the Select statements recently to see if
the needed indexes all exist?