[wp-hackers] Improving WordPress' Performance WAS Changing MySQL minimum version

[Ryan Boren]

Komra Moriko wrote:
> Hi Ryan,
> 
> Using 2.04 (2.1 not ready yet, right?)
> 
> These are the ones I added, made a huge difference in performance -- we 
> have 17000 posts and fairly heavy traffic.
> I checked my slow query log, and used EXPLAIN to examine what was going 
> on and added indexes for columns referenced in where statements:
> 
> CREATE INDEX cat_name ON wp_categories (cat_name);
> CREATE INDEX post_date ON wp_posts (post_date);
> CREATE INDEX post_date_gmt ON wp_posts (post_date_gmt);
> CREATE INDEX post_status ON wp_posts (post_status);


2.1 adds the following:

   KEY type_status_date (post_type,post_status,post_date,ID)

I don't know if we need post_date_gmt.  We need to audit our queries to 
see.  I thought we used post_date when we needed to key.

As for cat_name, we only key off of that when retrieving all links or 
posts for a specific category specified by name.  Most templates don't 
do that.  I guess we can add a cat_name key for those few themes  that 
do query by cat_name.

> Additionally, I have hacked the WP admin interface to make it more 
> usable, adding more metadata to to the list of posts, and adding a 
> browse category drop down and a separate Drafts page. Don't know if 
> these can be incorporated into the new version, or if other UI 
> optimizations are already taking place...

2.1 allows browsing by category on the Manage->Posts screen.  There is 
talk of providing separate pages for drafts and private posts.  That 
probably won't make 2.1 though.

Ryan