[wp-hackers] slow queries in 1.2

[John Sinteur]

I've been keeping mysql running with the 'log-slow-queries' option, and  
one query stands out:

# Time: 39  Lock_time: 2  Rows_sent: 6605  Rows_examined: 6626
SELECT DISTINCT
         ID, category_id, cat_name, category_nicename,  
category_description, category_parent
         FROM wp_categories, wp_post2cat, wp_posts
         WHERE category_id = cat_ID AND post_id = ID AND post_id IN  
(8229,8228,8225,8220,8215,8210,8208,8203,8199,8197,8194,8193,8192,8189,8 
187,8186,8184,8180,8179,8178,8172,8163,8166,8161,8157,8155,8153,8151,815 
0,8149,8147,8146,8145,8144,8143,8139,8138,8136,8133,8132,8129,8126,8125, 
8124,8119,8115,8113,8110,8109,8105,8104,8103,8101,8094,8091,8089,8088,80 
87,8083,8081,8079,8077,8073,8072,8071,8068,8067,8065,8064,8063,8060,8059 
,8058,8057,8056,8054,8052,8048,8044,8043,8041,8038... insane long list  
of ID's)

I've tracked this down, and it's in wp-blog-header.php, around line 470

I haven't looked at *why* this query is done, so I don't know how the  
retrieved information is used, but it appears the query can be  
rewritten a bit:

from:

     $dogs = $wpdb->get_results("SELECT DISTINCT
         ID, category_id, cat_name, category_nicename,  
category_description, category_parent
         FROM $tablecategories, $tablepost2cat, $tableposts
         WHERE category_id = cat_ID AND post_id = ID AND post_id IN  
($post_id_list)");

to:

     $dogs = $wpdb->get_results("SELECT DISTINCT
         post_id, category_id, cat_name, category_nicename,  
category_description, category_parent
         FROM $tablecategories, $tablepost2cat
         WHERE category_id = cat_ID AND post_id IN ($post_id_list)");

This way it doesn't touch the wp_posts table. I'm going to keep an eye  
out on the slow-queries log and see if it makes a difference.

In the mean time:

- can anybody tell me what information the query is retrieving and why?
- I'm slighty over 8200 posts right now in my weblog - is this high? Do  
we know how well wordpress scales?
- any database issues I might look at while I'm at it? Say, porting to  
postgresql?

-John