Blog with 50k posts / 300k comments – slow msyql queries

I’m pretty new to this mysql stuff but i’ve been doing alot of reading.

I setup log slow queries to shows queries that are slow than 1. I stripped the site of all plugins except the caches. Here’s some examples:

->  Somebody looking at posts by author?

# Query_time: 2  Lock_time: 0  Rows_sent: 20  Rows_examined: 56540
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  WHERE 1=1  AND (wp_posts.post_author = 2) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish')  ORDER BY wp_posts.post_date DESC LIMIT 4840, 20;

-> Viewing a single post?

# Query_time: 3  Lock_time: 0  Rows_sent: 1  Rows_examined: 165587
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category'  AND wp_term_taxonomy.term_id IN ('77', '1', '58', '63', '64')  AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 1;

-> Viewing a category

# Query_time: 2  Lock_time: 0  Rows_sent: 20  Rows_examined: 145236
SELECT SQL_CALC_FOUND_ROWS  wp_posts.* FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)  WHERE 1=1  AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('1') AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 21340, 20;

These queries eat up almost all of the CPU time and there’s hundreds of them running. Luckily the server is surviving. The problem comes when I try to Publish new posts and Approve New comments. Those two actions bring down the server.

I think my problem is that the normal wordpress queries are not using indexes to return data. I ran myisamchk -r on all the MYI tables. It fixed the indexes but i still get those slow queries above.

I’ve been searching but i cannot find any wordpress documentation for dealing with large databases and adding indexes to make queries faster. Can anybody point me int he right direction?